SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enable first run as truncate


Enable first run as truncate

Author
Message
koti.raavi
koti.raavi
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2099 Visits: 422
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!
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3674 Visits: 531
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search