Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

AccountImportJSON for DB integration is throwing error

LearningStar
New Contributor III
New Contributor III

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

LearningStar_0-1708605212399.png

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

LearningStar_1-1708605388645.png

 

 

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

Regards

Learning Star

14 REPLIES 14

naveenss
All-Star
All-Star

Hi @LearningStar ,

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

 

Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

Raghu
Honored Contributor
Honored Contributor

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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

LearningStar
New Contributor III
New Contributor III

Hi,

 

Please find the attached logs.

Raghu
Honored Contributor
Honored Contributor

did you tried above query , if yes please confirm?

i changed status


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

LearningStar
New Contributor III
New Contributor III

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

Raghu
Honored Contributor
Honored Contributor

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

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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

LearningStar
New Contributor III
New Contributor III

 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

 

 

Raghu
Honored Contributor
Honored Contributor

@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"/>


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

LearningStar
New Contributor III
New Contributor III

@Raghu : 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>

Raghu
Honored Contributor
Honored Contributor

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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

LearningStar
New Contributor III
New Contributor III

@Raghu : 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>

Raghu
Honored Contributor
Honored Contributor

logs share please


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Raghu
Honored Contributor
Honored Contributor

@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>


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

rushikeshvartak
All-Star
All-Star

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>

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