Hi Guys,

I took a look at the MSDTC article. I have been down that path before during setup.

I guess the question is why use a transaction at all when doing a large ETL jobs.

Here are some ideas that might be quicker overall.

1 - If you can get away with an UPSERT, do it. It is better than trying to roll back a huge load.

It is also restartable. Use a hosptial table to direct errored out rows for research of post processing.

2 - Create a database snapshot before the load. All changed records will go to this sparse file.

When the job errors out, stop the ETL. Use a database restore to put the data back to a steady state.

I ended up rewriting a database warehousing project recently. Plan 1 worked our very well since I did a singe pass of 10 M rows and then did several passes against the hospital table to fix data integrity issues.

In summary, always question why you are going down a path before you start.

Good luck