OLEDBDestination in SSIS does not report low-level error message

  • Article

I got a new one where we found that SSIS does not report the actual low-level error message while loading data inside SQL Server using either OLEDBDestination or SQLDestination. Say, if you are trying to load data into a SQL Server table from a text file and you expect some errors in the data that will result in some rows not being able to insert. Typically, this will be due to Check Constraints, Foreign Key constraints, and Unique Key constraints.

  • Whenever there is a constraint violation, SQL Server will raise error like "The INSERT statement conflicted with the CHECK constraint "<XX>". The conflict occurred in database "<XX>", table "<XX.XX>", column '<XX>'."  There will be similar errors for PK and FK errors.
  • But inside SSIS, we can only get an errorcode when such failures occur.
  • You can add a script as per: Enhancing an Error Output with the Script Component  (SQL BOL:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/f7c02709-f1fa-4ebd-b255-dc8b81feeaa5.htm)
    but this just reports the error message that is listed under Integration Services Error and Message Reference (SQL BOL: ms help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2c825c07-5074-42ad-90ea-0dc5a588dcf7.htm)
    like "The data value violates integrity constraints."
  • The ability to capture such a low level error message would have been very good especially if there are lot of records being imported.
  • Checked with the dev team and they confirmed that there is no way of achieving this currently in SQL 2005 builds.

Our options: Add some lookup tasks to filter out the data that can get constraint violation issues. This is to ensure some proactive checking to disallow the troublesome data to reach upto destination.