Click HERE to see how Saviynt Intelligence is transforming the industry. |
02/22/2024 04:40 AM
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
02/22/2024 05:04 AM
Hi @LearningStar ,
The XML seems good. Can you please also share the connection parameter screenshot and the log snippet.
02/22/2024 05:15 AM - edited 02/22/2024 05:18 AM
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
02/22/2024 05:23 AM
02/22/2024 05:24 AM - edited 02/22/2024 05:25 AM
did you tried above query , if yes please confirm?
i changed status
02/22/2024 05:43 AM - edited 02/22/2024 05:43 AM
Hey @Raghu : Error got changed while running job I can see this error
XML document structures must start and end within the same entity.
02/22/2024 05:44 AM
Thanks for update, you use your xml and just replace below tag alone andtry
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/>
02/22/2024 05:47 AM - edited 02/22/2024 05:49 AM
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
02/22/2024 05:50 AM - edited 02/22/2024 05:50 AM
@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"/>
02/22/2024 05:58 AM
@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>
02/22/2024 05:58 AM
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
02/22/2024 06:15 AM
@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>
02/22/2024 06:22 AM
logs share please
02/22/2024 07:59 AM
@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>
02/22/2024 08:49 PM
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>