Import job with sql server as source, to get incremental (delta) data changes using LAST_RUN

  • 48Views
  • Last Post 02 April 2020
0
votes
robert fisher posted this 19 March 2020

After initial data sync with SQL Server as source, we are trying to use an import job to pick up delta (new/changed) data. We are comparing the dateUpdated column (datetime datatype in sql server tables) with LAST_RUN. It didnt pick up 4-5 hours of modified data as the Timezone is EST is behind UTC. We informed the client of this requirement but as this database is managed by an ERP system and their business processes need the data to be in EST timezone, they have refused to change the timezone. So now we are trying to adjust the dateUpdated value to UTC and then comparing with LAST_RUN value. But the filter criteria dialog it only allows selecting LAST_RUN on one side and the dateUpdated column on the other side but doesnt allow editing it or using it in an expression.  Even Informatica cloud provides Filter-expression where one can enter an expression with a column for the same situation. See this link: https://www.mydatahack.com/informatica-cloud-incremental-load-with-data-synchronisation/

Surely this must be a common situation as LAST_RUN is fixed at UTC , if that cant be changed, then just changing the other side of the equation will do it. But where is the option for that ? 

Please indicate how this can be done or how OTHER USERS have dealt with this situation as everybody cannot have timezone as UTC. Just this small part not working is preventing us from using Skyvia successfully.

Order By: Standard | Newest | Votes
0
votes
Dmitriy Muzurov posted this 20 March 2020

Hello Robert,

Thank you for submitting a ticket with us.

LAST_RUN keyword is developed to work in UTC only. This is by design and can't be changed.

We can offer you to create a filter by either "createdat" or "updatedat" column and select >=YESTERDAY:

The package should be scheduled to run each day at the same time.

Should you have any questions, do not hesitate to contact us back.

0
votes
robert fisher posted this 20 March 2020

Thanks for the response.

The requirement is to run this package every hour to keep downstream system almost in sync , so...

LastModifiedDate >= Yesterday

...is not suitable for that as re-sending records already sent earlier can make duplicates. So that is not useful.

We understand that you cannot change the UTC timezone and other functionality associated with last_run. But on the left side where you choose the lastModifiedDate column, there, all that needs to be done is allow an expression like the following (example is using SQL Server syntax)

dateadd(MINUTES, 240, LastModifiedDate)            >=        LAST_RUN

That's all that is needed to adjust the 4 hour difference between our server's TimeZone and your LAST_RUN (UTC) time.

Thats all it needs to overcome the current limitation. This is probably a minor UI tweak which could benefit MANY MANY skyvia users whose server timezone is not set to UTC (which is very likely more than 50%)

This could be a much NEEDED FEATURE request.

Thanks

0
votes
Dmitriy Muzurov posted this 02 April 2020

Hello Robert,

Thank you for your reply.

We raised a ticket with our development team to investigate if this feature can be implemented.

Once there are any details, we will update the post.

Close