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.