Database import failure

[Joon]

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>

[rushikeshvartak]

Which import is this ?

it seems account import if yes then Systems is missing

[Joon]

Hi @rushikeshvartak ,

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

[rushikeshvartak]

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>

[Joon]

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?

[amit_krishnajit]

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. 

[rushikeshvartak]

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

[Joon]

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

and it's not duplicated...

Can I get any advice ?

[amit_krishnajit]

Try query like the following:

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

[Joon]

Hi @amit_krishnajit ,

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

also in the endpoint..

[rushikeshvartak]

Endpointkey is 1588

[Joon]

I am Sorry for making you confused,

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

[rushikeshvartak]

Your query is wrong 

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

[rushikeshvartak]

@Joon Is this resolved ?