Performing Incremental Import of Users with a Timestamp Column in Oracle DB

[gokul]

We have a Timestamp column in an Oracle DB with a default format, and we need to use it as an incremental column for importing users in Saviynt.

However, while performing comparisons, we encounter an "ORA-01843: not a valid month" error due to the timestamp format disparity between the Oracle DB(DD-MON-RR HH.MI.SSXFF AM) and Saviynt DB (YYYY-MM-DD HH24:MI:SS.FF1).

How can we keep both timestamp format to be the same?

I attempted to directly change the format in the user import XML as shown below, but it didn't work

<mapper description="This is the mapping field for Saviynt Field name" dateformat="timestamp" incrementalcolumn="TO_CHAR(AUDIT_STAMP, 'YYYY-MM-DD HH24:MI:SS.FF1')">

Any assistance with this matter would be greatly appreciated.

[sudeshjaiswal]

Hello @gokul,

If Your import is a select query, then please try the query as below in the import select statement itself and then for incremental column you can try keeping just 'updatedate'
TO_CHAR(AUDIT_STAMP,'YYYY-MM-DD') as updatedate ( to be used in select query while fetching the attribute)

For Ref:- 
https://docs.saviyntcloud.com/bundle/Database-v23x/page/Content/Configuring-the-Integration-for-Impo... 

[AG]

Thanks @sudeshjaiswal - will try. Can you confirm if  we must use "updatedate" only and not any other attribute?

[sudeshjaiswal]

Hello @AG,

It should be updatedate only.

[gokul]

Hellow @sudeshjaiswal 

Following your instructions, we have made updates to our select statement by including the 'updatedate' field and configured it as an incremental column in the userimport xml.

However the process is not functioning as expected. During the initial run, we obtained a total of 6 records, even without making any change, the subsequent run has also resulted the same set of 6 records. As per the incremental approach, we were expecting to receive no new records in this situation.

Sample User Import XML used is given below.

<dataMapping>

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="EMPLID">

<![CDATA[SELECT DISTINCT column1, column2, AUDIT_STAMP, TO_CHAR(AUDIT_STAMP, 'YYYY-MM-DD HH24:MI:SS.FF1')AS updatedate, column3, EMPLID FROM target_table]]>

</sql-query>

<importsettings>

<zeroDayProvisioning>true</zeroDayProvisioning>

<generateEmail>false</generateEmail>

<userNotInFileAction>NOACTION</userNotInFileAction>

<checkRules>true</checkRules>

<buildUserMap>false</buildUserMap>

<userReconcillationField>username</userReconcillationField>

</importsettings>

<mapper description="This is the mapping field for Saviynt Field name" dateformat="timestamp" incrementalcolumn="updatedate">

<mapfield saviyntproperty="username" sourceproperty="column1" type="character"></mapfield>

<mapfield saviyntproperty="employeeid" sourceproperty="EMPLID" type="character"></mapfield>

<mapfield saviyntproperty="firstname" sourceproperty="column2" type="character"></mapfield>

<mapfield saviyntproperty="customproperty1" sourceproperty="column3" type="character">

</mapfield>

<mapfield saviyntproperty="customproperty13" sourceproperty="AUDIT_STAMP" type="timestamp"></mapfield>

</mapper>

</dataMapping>

[pmahalle]

Hi @gokul ,

Can you follow below steps and try.

1. Make sure, you fetching column/field (UPDATEDDATE ) from target in select Query present in user import xml.

2. dateformat and incrementalcolumn should be present as an attribute under mapper tag as below. incrementalcolumn value should be the target column holding updatedate:

<mapper description="This is the mapping field for Saviynt Field name" dateformat="timestamp" incrementalcolumn="UPDATEDDATE">

3. Map the target field holding recent update date in updatedate column of user in Saviynt like below:

<mapfield saviyntproperty="updatedate" sourceproperty="UPDATEDDATE" type="date"></mapfield>

[sudeshjaiswal]

Hello @gokul @AG ,

Please try with the below XML,
<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="EMPLID">
<![CDATA[SELECT DISTINCT column1, column2, AUDIT_STAMP, TO_CHAR(AUDIT_STAMP, 'YYYY-MM-DD HH24:MI:SS.FF1')AS updatedate, column3, EMPLID FROM target_table]]>
</sql-query>
<importsettings>
<zeroDayProvisioning>true</zeroDayProvisioning>
<generateEmail>false</generateEmail>
<userNotInFileAction>noaction</userNotInFileAction>
<checkRules>true</checkRules>
<buildUserMap>false</buildUserMap>
<userReconcillationField>username</userReconcillationField>
</importsettings>
<mapper description="This is the mapping field for Saviynt Field name" dateformat="timestamp" incrementalcolumn="updatedate">
<mapfield saviyntproperty="username" sourceproperty="column1" type="character"></mapfield>
<mapfield saviyntproperty="firstname" sourceproperty="column2" type="character"></mapfield>
<mapfield saviyntproperty="customproperty13" sourceproperty="AUDIT_STAMP" type="timestamp"></mapfield>
<mapfield saviyntproperty="updatedate" sourceproperty="updatedate" type="date"></mapfield>
<mapfield saviyntproperty="customproperty1" sourceproperty="column3" type="character"></mapfield>
<mapfield saviyntproperty="employeeid" sourceproperty="EMPLID" type="character"></mapfield>
</mapper>
</dataMapping>

Thanks

[rushikeshvartak]

<mapper description="This is the mapping field for Saviynt Field name" dateformat="date" incrementalcolumn="updatedate">

[gokul]

Thanks team - @rushikeshvartak @sudeshjaiswal @pmahalle 

I have a few questions below. Kindly clarify them for me:

1. Is it mandatory to map the incremental column "updatedate" with a Saviynt property?
2. Regarding incremental userimport, can we use 'timestamp' as a date format? Since we run this job every 2 hours, we want the incremental to be based on the timestamp not on the date.
3. Could you please provide an explanation of how the incremental update process works? My understanding is that it compares the 'updatedate' with the timestamp of the job's last run and retrieves new records where 'updatedate' > job last run timestamp. Is my understanding correct?

Thank You

[sudeshjaiswal]

Hello @gokul,

1. Is it mandatory to map the incremental column "updatedate" with a Saviynt property?
   
   Answer- Yes, You can specify the name of the date or datetime column of the target database in the incrementalcolumn attribute. The date or datetime column may not be updatedate in all target databases, it may vary. 
   when the incrementalcondition tag is not specified and the incrementalcolumn attribute is set as updatedate by default.
   
   
2. Regarding incremental userimport, can we use 'timestamp' as a date format? Since we run this job every 2 hours, we want the incremental to be based on the timestamp not on the date.
   

   Answer:- Consider the following scenarios, when the incrementalcondition tag is not specified and the incrementalcolumn attribute is set as updatedate:

   • When dateformat is set as timestamp, the updatedate comparison for incremental import is based on timestamp (yyy-mm-dd hh:mm:ss.0).

   • When dateformat is set as date, the updatedate comparison for incremental import is based on date (dd-MMM-yy format).

3. Could you please provide an explanation of how the incremental update process works? My understanding is that it compares the 'updatedate' with the timestamp of the job's last run and retrieves new records where 'updatedate' > job last run timestamp. Is my understanding correct?
   
   Answer : No, It compare the updatedate timestamp from the target application.