共用方式為

How to Decipher/Understand SSIS Error Code?

  • 發行項

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.

Comments

  • Anonymous
    July 28, 2008
    Great find! I just wanna clarify one thing. This is coming from the perspecetive where logging for SSIS is turned off (which is the default). So in the SQL job log you just get the numeric error code. But, if you have SSIS logging turned on this becomes unncessary.

  • Anonymous
    July 28, 2008
    Its not comming in my case..... even when i am finding for the DTS_E_OLEDBDESTINATIONADAPTERSTATIC_CANTCONVERTVALUE in the dts.h its not showing any things can u tell me what wud be the reason????

  • Anonymous
    July 28, 2008
    Smitha, I am not really sure I understand your question. You need to lookup dtsmsg.h, not dts.h. dtsmsg.h has all the constants and it has descriptions for the error constants.

  • Anonymous
    August 01, 2008
    In my previous post , I explained how you can find out the error description for the error id that is

  • Anonymous
    August 01, 2008
    In my previous post , I explained how you can find out the error description for the error id that is

  • Anonymous
    August 01, 2008
    In my previous post , I explained how you can find out the error description for the error id that is

  • Anonymous
    September 23, 2008
    That file is NOT located under my directory. thanks.. but no thanks.

  • Anonymous
    September 23, 2008
    The comment has been removed

  • Anonymous
    December 09, 2008
    The comment has been removed

  • Anonymous
    January 05, 2009
    Awesome :)  Thanks for the help!

  • Anonymous
    August 24, 2009
    a better approach: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/aef94061-d246-41cd-b877-f0cd7906dabe/

  • Anonymous
    September 14, 2010
    Thanks for the post, i got what i need

  • Anonymous
    September 24, 2010
    The comment has been removed