I have a flat file loading process that first loads to a staging table and then we process updates into the database from there. When and if this loads fail it is usual one of 2 things. Either there is a problem with the file itself or there is a database deadlock.
What I want to be able to do is intercept the error type in the data flow, if it is a deadlock then I want the package to error as usual and let the retries on the SQL Agent Job run as usual in the hope that the deadlock is gone on the subsequent retries. If the error is not a deadlock, then I want to move the file into a failed folder so the SQL Agent Retry will kick in and the next file in turn will be processed.
Has anyone got any ideas how I can intercept the error type to help me build this deign pattern.
Thanks
Tim.
Which version of SSIS are you running? Things changed in 2016 to make this task easier.
Funnily enough it is 2016. I have just begun to experiment with the OnError events and using a script task to see what information I can obtain from the error.
What else do you have in mind?
Thanks
Tim.