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.