Saviynt Forums

gokul · ‎08/09/2023

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 · ‎08/10/2023

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

If you find the above response useful, Kindly Mark it as "Accept As Solution".

AG · ‎08/10/2023

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

sudeshjaiswal · ‎08/10/2023

Hello @AG,

It should be updatedate only.

If you find the above response useful, Kindly Mark it as "Accept As Solution".

gokul · ‎08/10/2023

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.

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

<generateEmail>false</generateEmail>

<userNotInFileAction>NOACTION</userNotInFileAction>

<buildUserMap>false</buildUserMap>

<userReconcillationField>username</userReconcillationField>

</importsettings>

</mapfield>

</mapper>

</dataMapping>

pmahalle · ‎08/10/2023

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:

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

Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂

sudeshjaiswal · ‎08/10/2023

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

If you find the above response useful, Kindly Mark it as "Accept As Solution".

rushikeshvartak · ‎08/10/2023

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

gokul · ‎08/14/2023

Thanks team - @rushikeshvartak @sudeshjaiswal @pmahalle

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

Is it mandatory to map the incremental column "updatedate" with a Saviynt property?
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.
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 · ‎08/15/2023

Hello @gokul,

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.
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).
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.

If you find the above response useful, Kindly Mark it as "Accept As Solution".

Saviynt Forums

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