EntitlementValueImportJob got failed

[LearningStar]

Hi Team,

I tried to do Entitlement import from database (SqL) it got failed stating below reason
Column count doesn't match value count at row 1 

however I tried to cross check it properly but could not see any mismatch, number of records are same which I am passing from database to Saviynt.

Mapper I used there 
<dataMapping>
<sql-query description="This is the Source Database Query">
<![CDATA[select 'demo' as applicationname,'demo' as endpointname,CarModel,carname,brandId,'1'as status from permission]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" entnotpresentaction="noaction" createentitlementtype="true" systems="demo">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="CarModel" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="carname" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="brandId" type="character"/>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"/>
</mapper>
</dataMapping>



my target look like this only 

[Amit_Malik]

HI @LearningStar , one thing that I can find is , you have status as Number but in query it's string.

Try this :

select 'demo' as applicationname,'demo' as endpointname,CarModel,carname,brandId,1 as status from permission

Thanks,

Amit

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

[LearningStar]

Hey Amit,

Getting same error after using your query , It says mismatch in error in respect to count.

[Amit_Malik]

Try this 

<dataMapping>
<sql-query description="This is the Source Database Query">
<![CDATA[select 'demo' as applicationname,'demo' as endpointname,CarModel,carname,brandId,1 as status from permission]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" entnotpresentaction="noaction" createentitlementtype="true">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"/></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="CarModel" type="character"/></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="carname" type="character"/></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="brandId" type="character"/></mapfield>
<mapfield saviyntproperty="entitlementvalues.displayname" sourceproperty="carname" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"/></mapfield>
</mapper>
</dataMapping>

[Amit_Malik]

You are not closing <mapfield. You can try closing those and see if that works. I have given query below , feel free to modify it but I hope closing mapfields and making status integer will solve it for you.

Thanks,

Amit

[LearningStar]

Amit I used your mapper even after that I got this error, I double checked all mapper tags are properly closed now , I got below error.

The element type "mapper" must be terminated by the matching end-tag "".

The element type "mapper" must be terminated by the matching end-tag "".

[Amit_Malik]

This:

<dataMapping>
<sql-query description="This is the Source Database Query">
<![CDATA[select 'demo' as applicationname,'demo' as endpointname,CarModel,carname,brandId,1 as status from permission]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" entnotpresentaction="noaction" createentitlementtype="true">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="CarModel" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="carname" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="brandId" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.displayname" sourceproperty="carname" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"></mapfield>
</mapper>
</dataMapping>

[Amit_Malik]

HI @LearningStar , this didn't work ? 

[rushikeshvartak]

<dataMapping>
	<sql-query description="This is the Source Database Query">
		<![CDATA[select 'demo' as applicationname,'demo' as endpointname,CarModel as entitlementtype,carname as entitlementvalue,brandId as entID,'1'as status from permission]]]>
	</sql-query>
	<mapper description="DEMO Import" deleteentitlementowner="true" entnotpresentaction="noaction" createentitlementtype="true">
		<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"/>
		<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
		<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="entitlementvalue" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="entID" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.customproperty3" sourceproperty="cp3" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"/>
	</mapper>
</dataMapping>

[rushikeshvartak]

Remove extra ] after table name

[LearningStar]

Hi Rushikesh,

I am getting below error while running job but I can see the results at the query level,(Target Side) not sure what is the reason of this.
Also note that I have already created security system, endpoint name along with entitlement type as well before import only

[LearningStar]

Hi @rushikeshvartak : Thanks for working on this but still I am facing this error In logs I can see entitlements are being added but I dont see any add entitlements moreover job results an error 

Logs given below :

quartzScheduler_Worker-4, called closeSocket(true)
2024-02-27 11:14:26,182 [quartzScheduler_Worker-4] DEBUG saviynt.EntitlementValueImportService - Entitlement Values Inserted/Updated= 2
2024-02-27 11:14:26,182 [quartzScheduler_Worker-4] DEBUG saviynt.EntitlementValueImportService - Existing Entitlement Owners Deleted= 0
2024-02-27 11:14:26,182 [quartzScheduler_Worker-4] DEBUG saviynt.EntitlementValueImportService - New Entitlement Owners Inserted= 0
2024-02-27 11:14:26,182 [quartzScheduler_Worker-4] DEBUG saviynt.EntitlementValueImportService - Entitlement Owners Updated= 0
2024-02-27 11:14:26,198 [quartzScheduler_Worker-4] DEBUG saviynt.EntitlementValueImportService - Exit in Method importData
2024-02-27 11:14:26,198 [quartzScheduler_Worker-4] DEBUG services.SaviyntCommonUtilityService - Enter runNextTrigger
2024-02-27 11:14:26,198 [quartzScheduler_Worker-4] DEBUG services.SaviyntCommonUtilityService - Exit runNextTrigger

[Raghu]

can you try below please

<dataMapping>
<before-import>
</before-import>
<sql-query description="This is the Source Database Query">
<![CDATA[select 'demo' as applicationname,'demo' as endpointname,CarModel as entitlementtype,carname as entitlementvalue,brandId as entID,'1'as status from permission]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="false" entnotpresentaction="noaction" createentitlementtype="true" systems="'demo'">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="entID" type="character"/>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"/>
</mapper>
</dataMapping>

[LearningStar]

No such property: for class: groovy.sql.GroovyRowResult Possible solutions: class, empty

@Raghu  status is integer value at target end  so I believe we  should  treated 1 instead of '1'

[Raghu]

you can try with 1 only ? and confirm

[rushikeshvartak]

<dataMapping>
<sql-query description="This is the Source Database Query">
<![CDATA[select 'demo' as applicationname,'demo' as endpointname,CarModel as entitlementtype,carname as entitlementvalue,brandId as entID,1 as status from permission]]>
</sql-query>
<mapper description="DEMO Import" deleteentitlementowner="true" entnotpresentaction="noaction" createentitlementtype="true">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="entID" type="character"/>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"/>
</mapper>
</dataMapping>

[LearningStar]

@rushikeshvartak : I have tried to use your provided mapper but still getting error on job run 
Column count doesn't match value count at row 1

[rushikeshvartak]

Remove entID mapping from mapper 

in DB connector you can’t map entitlement id

https://forums.saviynt.com/t5/identity-governance/oracle-db-entitlement-import-duplicate-entry/m-p/1...

[SDBeltran]

I am having the very same issue described above. I wonder if there was ever a resolution. The only difference is that I'm working w/oracle as opposed to MS SQL as it looks above, but same situation, I do get results from the query in the database engine, but it looks like Saviynt is not parsing right the statement to be sent to the DB -- either adding or missing stuff.