AccountImportJSON for DB integration is throwing error

[LearningStar]

Hi Everyone,

I am struck while importing accounts using database account import. I am able to see the results while executing query but when I ran the job it shows this error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

Below mapper I am using it 
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select name as accountname,'HRMS' as securitysystem, 'Human Resource' as endpoint ,'Role' as entitlementtype,access as entitlementvalue,1 as status,BusinessCategory from hrmsuser]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="false" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="BusinessCategory" type="character"/>

</mapper>
</dataMapping>

below is my database schema

Please note that I have already created the security system and endpoint and entitlment type with these names

Regards

Learning Star

[naveenss]

Hi @LearningStar ,

The XML seems good. Can you please also share the connection parameter screenshot and the log snippet. 

[CR]

below one try and please share the logs

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[
select name as accountname,'HRMS' as securitysystem, 'Human Resource' as endpoint ,'Role' as entitlementtype,access as entitlementvalue,'1' as status,BusinessCategory from hrmsuser]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="false" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="BusinessCategory" type="character"/>

</mapper>
</dataMapping

[LearningStar]

Hi,

Please find the attached logs.

[CR]

did you tried above query , if yes please confirm?

i changed status

[LearningStar]

Hey @CR : Error got changed while running job I can see this  error 
XML document structures must start and end within the same entity.

[CR]

Thanks for update, you use your xml and just replace below tag alone andtry

<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>

[LearningStar]

 Hey,

I am using below final mapper 

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select name as accountname,'HRMS' as securitysystem, 'Human Resource' as endpoint,'1' as status,BusinessCategory from hrmsuser]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="false" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="BusinessCategory" type="character"/>
</mapper>
</dataMapping>

got same error 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

[CR]

@LearningStar  you can use what ever you have backup earlier syntax  error xml but one line need change status line i given that add it try again

old: <mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"/>

new

<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>

[LearningStar]

@CR : I tried that also but even after using your suggested mapper I am still getting same error which I mentioned in my post, not sure what is the reason.

Kindly review once let me know if I missed anything out from your Instructions

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select name as accountname,'HRMS' as securitysystem, 'Human Resource' as endpoint,'1' as status,BusinessCategory from hrmsuser]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="false" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="BusinessCategory" type="character"/>
</mapper>
</dataMapping>

[CR]

you query is wrong please below

select name as accountname,'HRMS' as systemname, 'Human Resource' as endpointname ,'Role' as entitlementtype,access as entitlementvalue,'1' as status,BusinessCategory from hrmsuser

[LearningStar]

@CR : I tried your query as well with list of mappers but I am getting same error I dont know what is wrong here 

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select name as accountname,'HRMS' as systemname, 'Human Resource' as endpointname ,'Role' as entitlementtype,access as entitlementvalue,'1' as status,BusinessCategory from hrmsuser]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="false" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="systemname" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="BusinessCategory" type="character"/>

</mapper>
</dataMapping>

[CR]

logs share please

[CR]

@LearningStar  i modified as working xml , you can below xml and if still issue share logs

<dataMapping>
<before-import ></before-import>
<sql-query description="This is the Source DB Query">
<![CDATA[
select name as accountname,'HRMS' as systemname, 'Human Resource' as endpointname ,'Role' as entitlementtype,access as entitlementvalue,'1' as status,BusinessCategory from hrmsuser

]]></sql-query>
<mapper description="This is the mapping field for Saviynt Field name" checkrules="false" buildusermap="false" accountnotinfileaction="noaction" deleteaccountentitlement="false" ifusernotexists="noaction" systems="'HRMS' ">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="systemname" 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="accounts.status" sourceproperty="status" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="BusinessCategory" type="character"/>
</mapper>
<after-import description="EMAIL,BATCH,SQL" ></after-import>
</dataMapping>

[rushikeshvartak]

You have missing securitysystem name in mapper 

<dataMapping>
	<before-import></before-import>
	<sql-query description="Import RV" uniquecolumnsascommaseparated="name" >
		<![CDATA[	
			select name as ACCOUNTNAME,'HRMS' as SECURITYSYSTEMNAME, 'Human Resource' as ENDPOINTNAME ,'Role' as ENTITLEMENTTYPE,access as ROLE,1 as ACCOUNTSTATUS,BusinessCategory from hrmsuser
	</sql-query>
	<mapper description="Import RV" accountnotinfileaction="suspend" 	deleteaccountentitlement="true" dateformat="date" incrementalcolumn="IMPORTDATE" systems="'HRMS'">
		<mapfield saviyntproperty="accounts.name" sourceproperty="ACCOUNTNAME" type="character"></mapfield>
		<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="SECURITYSYSTEMNAME" type="character"></mapfield>
		<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="ENDPOINTNAME" type="character"></mapfield>
		<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="ENTITLEMENTTYPE" type="character"></mapfield>
		<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="ROLE" type="character"></mapfield>
		<mapfield saviyntproperty="accounts.status" sourceproperty="ACCOUNTSTATUS" type="number"></mapfield>
			<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="BusinessCategory" type="character" />
	</mapper>
	<after-import description="EMAIL,BATCH,SQL"></after-import>
</dataMapping>