SSIS: Use Two OLE DB Destination Adapters to Catch Insertion/Constraint Errors

If you are using SQL Server Integration Server (SSIS) packages to insert large volumes of data, you have undoubtedly encountered incorrectly formatted or typed data in your data source including precision errors, conversion errors, and primary key/foreign key constraint errors etc.

To avoid package failure, you can choose to redirect error output to an alternate destination rather.  However, if you are using “Table or view – fast load” as your insertion method, it can be difficult to see which rows are actually the offenders in your redirected output.

One way to get around this is to use two OLE DB Destination adapters with different table access modes and then redirect the output.

On the first OLD DB Destination adapter, choose “Table or view – fast load” as the access mode, but set the maximum commit size to a specific number appropriate for your data set size.  Remember the smaller the commit size the slower the package will run.  In my particular case, I found a commit size of 5000 was appropriate for the data source.

Next, connect the error output from your first OLE DB Destination adapter to a second OLE DB Destination adapter rather than directly to a “bad input” file or database destination.  On this adapter, choose “Table or view” as the access mode which will insert records into the database one-by-one (another reason why you will have to tune your first adapter’s commit size to get optimal balance between performance and error handling).

Finally, take the error output from this second OLE DB Destination adapter and connect it to your “bad input” destination.

When it runs, the data batch that has the bad data on the first adapter will get redirected to the second adapter rather than having the whole commit size set being dumped to your error output file.  And because it is inserting one-by-one with “Table or view” access, you will only get the “real” errors dumped out to your error destination.

 

This entry was posted in MS SQL Server, SSIS. Bookmark the permalink.

5 Responses to SSIS: Use Two OLE DB Destination Adapters to Catch Insertion/Constraint Errors

  1. Rohan Cragg says:

    Wow, thanks so much for this one. I’d never have thought to do this.

  2. Anand says:

    I am getting The “data value violates integrity constraints. ” error for primary key and Null constarints nd other errors also. Is there any way to get exact errors

  3. Greg says:

    SSIS isn’t know for the best error details, but you can definitely trace the package log looking for a sequence of “OnError”s, or, within in a Visual Studio debugging session, look for the sequence of red exclamation marks. Also, don’t forget because you have two data connectors, you will need to sync every schema change to both connectors – for example, when you change a column type from the default varchar type in one, you have to remember to change it down the line.

  4. Rich Tasker says:

    I like this solution. Do you happen to remember what version you did it in? I’m trying to do this in 2005 and having problems saying my first oledb destination can’t be fastload and redirect output. Maybe you can do it in an later version. Thanks

  5. Rich Tasker says:

    Forget it. I didn’t have the commit size set. I just set the rows per batch.

Leave a Reply

Your email address will not be published. Required fields are marked *