John Mitchell-245523 (3/5/2014)

---

Koen Verbeeck (3/5/2014)

---

Interesting question, but I do not agree entirely with the answer.

Setting the commit size to a lower value will commit batches before the entire load is done. What if something fails and I want to roll everything back?

Splitting the load up in smaller chunks but in manageable transactions seems to make more sense.

Koen, I don't understand. Setting the commit size to a lower value is having SSIS split the load into more manageable chunks for you, isn't it? If there's a failure, you're not going to be able to roll the whole thing back in either situation (unless, of course, the failure occurs in the first batch).