We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Database import failure

Joon
Regular Contributor II
Regular Contributor II

Hello Team,

I am getting an error below while running saviynt 4 saviynt database import.

For input string: "[:]"
 
 
 
Here's my XML.  Can you please let me know what caused this error ?
<?xml version="1.0"?>
<dataMapping>
<before-import>
</before-import>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[
SELECT DISTINCT
name AS name,
accountid AS accountid,
'AccessLevel' AS ettypename,
accounttype AS entvalue,
'SAP Litmos' AS systemname,
'SAP Litmos' AS endpointname
from
accounts
where status like '1' and accounttype is not null and endpointkey = 824
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="suspend" deleteaccountentitlement="false" ifusernotexists="noaction" incrementalcolumn="updatedate" dateformat="timestamp">
<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="systemname" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="ettypename" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entvalue" type="character"/>
<mapfield saviyntproperty="accounts.accountid" sourceproperty="accountid" type="character"/>
</mapper>
<after-import description="EMAIL,BATCH,SQL">
</after-import>
</dataMapping>
13 REPLIES 13

rushikeshvartak
All-Star
All-Star

Which import is this ?

it seems account import if yes then Systems is missing


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Joon
Regular Contributor II
Regular Contributor II

Hi @rushikeshvartak ,

Yes It is,  should I put system in mapper description? I already put in the mapfield though.

Its required in mapper


@Joon wrote:

Hi @rushikeshvartak ,

Yes It is,  should I put system in mapper description? I already put in the mapfield though.


<dataMapping>
	<before-import></before-import>
	<sql-query description="SAP Litmos Database Accounts and Account to Entitlement Import" uniquecolumnsascommaseparated="name">
		<![CDATA[
                                   SELECT DISTINCT
name AS name,
accountid AS accountid,
'AccessLevel' AS ettypename,
accounttype AS entvalue,
'SAP Litmos' AS systemname,
'SAP Litmos' AS endpointname
from
accounts
where status like '1' and accounttype is not null and endpointkey = 824
]]>
	</sql-query>
	<mapper description="SAP LitmosDatabase Accounts and Account to Entitlement Import" accountnotinfileaction="suspend" deleteaccountentitlement="true" dateformat="date" incrementalcolumn="updatedate" systems="'SAP Litmos'">
		<mapfield saviyntproperty="accounts.name" sourceproperty="name" type="character"/>
		<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="systemname" type="character"/>
		<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
		<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="ettypename" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entvalue" type="character"/>
		<mapfield saviyntproperty="accounts.accountid" sourceproperty="accountid" type="character"/>
	</mapper>
	<after-import description="EMAIL,BATCH,SQL"></after-import>
</dataMapping>

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Joon
Regular Contributor II
Regular Contributor II

Thanks @rushikeshvartak ,

Now I can see this error.

Duplicate entry '1558-DQRC9la_3vEQr8oGqZLNTw2-tester@coupang.com' for key 'ENDPOINTKEY'
Can I get any advice on what caused this?

 

There is a uniqueness constraint on the accounts table which is a combination of ENDPOINTKEY-ACCOUNTID-ACCOUNTNAME. From the above-mentioned error, it looks like you have duplicate entries in the accounts table for the ENDPOINTKEY 1558 for the account tester@coupang.com.

You may need to clean up some of the account metadata for the duplicate accounts. 

 

Thanks,
Amit

Are you using account id mapping ?

can you check accountid mentioned in error not populated for 2 accounts if yes then for one them append -old using update query


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Joon
Regular Contributor II
Regular Contributor II

Hello Amit,

the weird thing is it's not even duplicated in the account table of the endpoint...

This is the real account  **sang.cho@coupang.com

Joon_1-1700725748414.png

and it's not duplicated...

Joon_2-1700725851283.png

Can I get any advice ?

 

 

Try query like the following:

select endpointkey, name, count(*) from accounts group by endpointkey,name having count(*) > 1

 

Thanks,
Amit

Joon
Regular Contributor II
Regular Contributor II

Hi @amit_krishnajit ,

I don't see any duplicates by your query also...

Joon_0-1700787498299.png

also in the endpoint..

Joon_1-1700787617347.png

 

 

Endpointkey is 1588


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Joon
Regular Contributor II
Regular Contributor II

I am Sorry for making you confused,

1588 was dev and Now I am working on prod..

Your query is wrong 

select endpointkey, name, count(*) from accounts where endpointkey=824 group by endpointkey,name having count(*) > 1

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@Joon Is this resolved ?


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.