I have an SSIS process that is built to push records to a web service that has some sporadic auth timeouts. My design is working well but we are running large batches of records 250,000 per batch over Millions of rows. What I'm looking to do is something similar to this post https://stackoverflow.com/questions/7076018/how-to-fail-ssis-component-on-invalid-rows but instead of failing the data flow task I want to to just complete successfully without processing more rows so it iterates on to my next loop iteration. Essentially once I get an error I know the rest for that batch will fail and I want to iterate my loop which I already have logic built to re-run failed records. Any thoughts on how to accomplish?
SSIS Data Flow Error Escape
ssis
Related Solutions
I am looking for the article that describes this behaviour better than I'll do here and will update if I can find it. I thought it was by Kirk Haselden or John Welch but no so luck.
If the batch size is set to 0, default, either the entire data flow will make it in or none of it. Generally, this is what you want as it gives the best performance and consistency.
If you have an exception in in the commit size (full or otherwise) and have set redirect error rows, the error row(s) and all the valid ones will all be mingled in the exception flow.
If your desire is to get all to "good" rows in and spit the "bad" ones to a staging area, the pattern described in the article is to try and commit successively smaller batches into the destination table. Eventually, you'll partition the inserts down to a valid sized batch or find the offending row(s) which are then stored to a staging area for review/cleanup.
The implementation is to have serial error redirect OLE DB destinations with decreasing rows per batch commit sizes, starting at say 10,000 and having the penultimate one with a size of 1. The last OLE DB destination would be to the staging/error table.
All of this assumes that you're using "fast load" version for the OLE provider. If you're "table or view" or "table or view from variable" you're already doing singleton inserts (and probably killing your performance) so the redirect stuff isn't needed to find a bad row.
I would design each package as a self-contained unit that can be rerun without any manual preparation or clean-up. Relying on SQL transactions can dramatically slow execution and as you noted does not cover all the non-SQL Tasks.
Best Answer
It is possible to process individual rows and redirect the records that failed. You could even validate records before you try to insert them.
How do you want it to work? Unless they specifically need to be processed together as a batch I would be processing individual records and storing failed records for correction and resubmission.