troubleshooting Question

How do I commit data each time after loading a few rows in SSIS 2016/2017

Avatar of amukta
amukta asked on
Microsoft SQL ServerSSIS
5 Comments1 Solution18 ViewsLast Modified:
We have multiple files that'd need to be loaded to a SQL Server table. A few files would have about 75-100 million and a few 50 million rows. we'd need to load from a flat file(.txt) .
I have a requirement to commit data after loading every 10000 rows. Overall there would be atleast 500-700 million rows to load.
I'm using a flat file source and OLEDB Destination and errors would be redirected to another table(error table).How can I accomplish this? can this be accomplishedin Task Factory Upsert Destination as well?
Do I specify 10000 in Rows per batch or Maximum Insert Commit size or is there a better solution that can accomplish this?
if there is an error in the 10000 rows that'd be committed and if one out of 10000 rows has an error and gets redirected to an error table, will all the 10000 rows rollback , if we check Maximum Insert Commit Size?

Also, there is a requirement, say if the first file is loaded successfully and if the load of the 2nd file fails at an nth row due to a connection issue, meaning nth row is not loaded, or the like, then, when I rerun the package, it should start loading from the nth row.

So, in summary there are 2 requirements,
1. Commit after loading every 10000 rows.
2. Load should start from the point of failure.

Could someone please suggest a best possible solution?
It's a bit urgent and hence I'm posting this on this site.

Any code/logic is greatly appreciated!

Thanks a million in advance!

Nakul Vachhrajani
Senior Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros