Hi,
I am doing the Account import from database but getting the following error after running the job.
Column count doesn't match value count at row 1
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
Following is the accounts import XML that I have configured -
<dataMapping>
<before-import>
</before-import>
<sql-query description="This is the Source DB Query">
<![CDATA[
SELECT Id as accountname,FirstName,LastName,Phone,Email,RoleId,ReportingResourceId, 'Zknow'as systemname, 'Zknow'as endpointname, '1' as accountstatus,'role' as entitlementtype,'' as entitlementvalue from Resource
]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="accountname" type="int, not null"></mapfield>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="FirstName" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty4" sourceproperty="LastName" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty6" sourceproperty="Phone" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty8" sourceproperty="Email" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty7" sourceproperty="RoleId" type="int"></mapfield>
<mapfield saviyntproperty="accounts.customproperty9" sourceproperty="ReportingResourceId" type="int"></mapfield>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="systemname" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"></mapfield>
<mapfield saviyntproperty="accounts.status" sourceproperty="accountstatus" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL">
</after-import>
</dataMapping>
Hi Raghu,
New Implementation
Id as accountname passed in the select query backend Id is int
I had modified the account.name to character but still getting error after running the job.
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
Hi Raghu,
FYR, I attached all the tables details & Colum and how today users are creating into system.
Hi Raghu,
I tried by passing the above xml but still getting the errors like: No such property: endpoint for class: groovy.sql.GroovyRowResult, No such property: entitlement for class: groovy.sql.GroovyRowResult, No such property: endpoint for class: groovy.sql.GroovyRowResult
I have changed the xml. I'm stuck with older error Column count doesn't match value count at row 1
<dataMapping>
<before-import>
</before-import>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name">
<![CDATA[
SELECT Id ,FirstName,LastName,Phone,Email as accountname,RoleId,ReportingResourceId,'Zknow' as systemname,'Zknow' as endpointname, '1' as accountstatus,'role' as entitlementtype,'' as entitlementvalue from Resource
]]>
</sql-query>
<importsettings>
<zeroDayProvisioning>false</zeroDayProvisioning>
<userNotInFileAction>NOACTION</userNotInFileAction>
<checkRules>false</checkRules>
<buildUserMap>false</buildUserMap>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name" checkrules="false" buildusermap="false" accountnotinfileaction="noaction" deleteaccountentitlement="false" ifusernotexists="noaction" dateformat="date" incrementalcolumn="accounts.customproperty5" systems="'Zknow' ">
<mapfield sourceproperty="systemname" saviyntproperty="securitysystems.systemname" type="character"></mapfield>
<mapfield sourceproperty="endpointname" saviyntproperty="endpoints.endpointname" type="character"></mapfield>
<mapfield sourceproperty="accountname" saviyntproperty="accounts.name" type="character"></mapfield>
<mapfield sourceproperty="Status" saviyntproperty="accounts.status" type="character"></mapfield>
<mapfield sourceproperty="ReportingResourceId" saviyntproperty="accounts.customproperty9" type="int"></mapfield>
<mapfield sourceproperty="RoleId" saviyntproperty="accounts.customproperty7" type="int"></mapfield>
<mapfield sourceproperty="Id" saviyntproperty="accounts.customproperty8" type="int"></mapfield>
<mapfield sourceproperty="Firstname" saviyntproperty="accounts.customproperty3" type="character"></mapfield>
<mapfield sourceproperty="Lastname" saviyntproperty="accounts.customproperty4" type="character"></mapfield>
<mapfield sourceproperty="Phone" saviyntproperty="accounts.customproperty6" type="character"></mapfield>
<mapfield type="character" sourceproperty="entitlementtype" saviyntproperty="entitlementtypes.entitlementname" ></mapfield>
<mapfield type="character" sourceproperty="entitlementvalue" saviyntproperty="entitlementvalues.entitlementvalue" ></mapfield>
<mapfield saviyntproperty="accounts.status" sourceproperty="accountstatus" type="character"></mapfield>
</mapper>
<after-import description="EMAIL,BATCH,SQL">
</after-import>
</dataMapping>
Hi,
I have modified xml but stuck getting the errors like: Column count doesn't match value count at row 1
The Account Import XML below with the logs.
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select Id,FirstName,LastName,Phone,Email,RoleId,ReportingResourceId,'Zknow' as SECURITYSYSTEM,'ZKnow' as ENDPOINT,null as entitlementtype,null as entitlementvalue from Resource]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="Id" type="int"/>
<mapfield saviyntproperty="accounts.customproperty2" sourceproperty="FirstName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="LastName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty4" sourceproperty="Phone" type="character"/>
<mapfield saviyntproperty="accounts.customproperty5" sourceproperty="Email" type="character"/>
<mapfield saviyntproperty="accounts.customproperty6" sourceproperty="RoleId" type="int"/>
<mapfield saviyntproperty="accounts.customproperty7" sourceproperty="ReportingResourceId" type="int"/>
<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"/>
</mapper>
</dataMapping>
