How to Decipher/Understand SSIS Error Code?

  • Article

If you have worked with SSIS, you may have been doing something like this, in case of errors, you saved the error code and the error column to a text file or some other media.

If you get the error within the IDE, that is cool, as you can get more readable information, but if you run the SSIS package in production environment, you does not get that readable information, only a numeric value.

Now you need to know what caused the error, so you read the error code. Unfortunately, the error code is not that clear.

To understand what is the meaning of the error code, you need these two things, Calculator (calc.exe), and dtsmsg.h file. dtsmsg.h by default is located under “C:\Program Files\Microsoft SQL Server\90\SDK\Include”. If you install SQL Server, you should have this file.

Let says, you get error –1071607689. To see what is the actual error:

  1. First open Calculator and switch to Scientific mode, and make sure the Decimal radio button is selected. Then type that number.
    image
  2. Click the Hex radio button, now you get a hex number, it is prefixed with eight Fs.
    image
  3. Copy the text, excluding the first eight Fs. In that example above, it is C0209077.
  4. Search that code inside dtsmsg.h. Now you get the constant and the description. That example above, the constant is DTS_E_OLEDBDESTINATIONADAPTERSTATIC_CANTCONVERTVALUE, and the description is ‘The data value cannot be converted for reasons other than sign mismatch or data overflow.’

One more error code that I found were –1071607675, using that method above, you can see that that code means there was data truncation.

Hopefully this will help you to figure out what was wrong with the SSIS package.