Enabling Checkpoints in your SSIS Packages

Who is online?  0 guests and 0 members
Home  »  Blogs  »  DevinKnight  »  Enabling Checkpoints in your SSIS Packages
 
4.29
/5
Avg: 4.29/5: (1 votes)

Comments (5)

MarkGStacey
MarkGStacey said:
There are several things you need to be careful of when creating a package that can resume. I am speaking from experience on packages that I've manually resumed. One of them is the commit size on data flows, if it's 0, then the data will be written as a single batch, and if it fails, then nothing is written. If it is say 5000, and 80000 rows have been written before the Data Flow task fails, then those rows that have been written are still in the destination table. Attempting to rerun the data flow will now insert duplicate records. Devin, I imagine that exactly the same will happen with checkpoints? Or is the engine THAT smart to know where a load is within a data flow? I doubt it.
1/23/2010
 · 
 · dixitmca likes this.
 
by
devinknight
devinknight said:
Yep you're absolutely right. If partial rows get inserted you could get duplicate data even with Checkpoints.
1/26/2010
 · 
 
by
nitya
nitya said:
Devin, By wrapping in a transaction, you can avoid duplicates right?
1/31/2010
 · 
 
by
devinknight
devinknight said:
Yes but one thing to be aware of with transactions is they do not work on all tasks. For example if you have transactions set on a package with a File System Task that moves a file the transaction will not move the file back if the package fails.
2/1/2010
 · 
 · dixitmca likes this.
 
by
dplovesbi
dplovesbi said:

Very useful informative article.

7/18/2011
 · 
 
by
Blogs RSS Feed

DevinKnight's latest blog posts

Blogs RSS Feed

Latest community blog posts