Enable first run as truncate

  • Hi ,

    I have a job created using ssis which runs everyday 5:30am CST to 11:30pm CST (15 min frequency)- Usually job will finish in 3-5mins

    Currently job is created for increment load based on tracking number, if tracking number already exist in detail table, it doesn't load data.

    I want to truncate data for first run -- I mean whatever tracking id's are exist in stage table should delete from detail table and load again --( Why we need to truncate is--we are sending data to one server -where data is not available yest but can be available today)

    Remaining all run's should be incremental ,  what is the best way to implement?, do we need to use time to check this?. like if time is between 5:30am and 5:45am then delete else truncate?

    let me know if any questions , Thanks!

  • koti.raavi - Monday, February 12, 2018 1:29 AM

    Hi ,

    I have a job created using ssis which runs everyday 5:30am CST to 11:30pm CST (15 min frequency)- Usually job will finish in 3-5mins

    Currently job is created for increment load based on tracking number, if tracking number already exist in detail table, it doesn't load data.

    I want to truncate data for first run -- I mean whatever tracking id's are exist in stage table should delete from detail table and load again --( Why we need to truncate is--we are sending data to one server -where data is not available yest but can be available today)

    Remaining all run's should be incremental ,  what is the best way to implement?, do we need to use time to check this?. like if time is between 5:30am and 5:45am then delete else truncate?

    let me know if any questions , Thanks!

    You could use normal stored procedure to do this.

    TRUNCATE the table ,  If you have a datetime field, get a max of that and store it in a variable and create a temp table , load the recent changed data  from source and compare the temp with main.

    Delete the records which are all present and reload the data from temp to main.

    We can achieve this in SSIS as well, but you'd need to try this in a Stored Proc first and later you can go with SSIS if any changes / additional features needed.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply