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 (13)
  1. Langston says:

    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.

  2. Smitha says:

    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????

  3. 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.

  4. In my previous post , I explained how you can find out the error description for the error id that is

  5. In my previous post , I explained how you can find out the error description for the error id that is

  6. In my previous post , I explained how you can find out the error description for the error id that is

  7. John says:

    That file is NOT located under my directory. thanks.. but no thanks.

  8. John,

    Depends on what OS, which Sql Server (32-bit or 64-bit), it might be on a different location. Also depends on what components you chose to install.

  9. Shawn says:

    This helped me greatly to come up with an automated approach.  I came up with two options one that modifies SSIS package(s) to store error description and another that can lookup the error description using the error code.  Of course, I created option 2 before I found option 1.

    Option 1 – (Ideal)

    Modify SSIS package(s) to capture error codes, lookup error description and then save to errors table

    http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

    Option 2 – if you’re not comfortable modifying or don’t have permission to modify SSIS package(s) that already store error codes in database

    1. Enable OLE Automation (to enable read of dtsmsg.h file)

    start -> programs ->MS sql 2005 -> configuration tools -> surfce area configuration -> Surface area conf for features -> Ole automation -> select the Enable check box and saved

    2. Create generic function to read in files

    http://www.simple-talk.com/code/WorkingWithFiles/uftReadFileAsTable.txt

    3. Create table to store error codes and descriptions

    create table DWErrorCodes

    (

    dwErrorCodeID smallint identity(1,1) primary key,

    ErrorCode varchar(10) not null,

    ErrorDescription varchar(200) not null

    )

    4. Fill in DWErrorCodes table with error code and description from dtsmsg.h file

    Select left(right(line,12),10) ErrorCode, ltrim(rtrim(substring(line,9,len(line)-9-23))) ErrorDescription

    from master.Dbo.uftReadfileAsTable(‘C:Program FilesMicrosoft SQL Server90SDKInclude’,’dtsmsg.h’)

    where line like ‘#define%’

    and len(ltrim(rtrim(replace(left(right(line,12),10),’ ‘,”)))) = 10

    5. Create view to join errors with error descriptions

    create view DWErrorsView as

    select e.DWErrorID, e.ExecutionTime, e.PackageName, e.TaskName, e.ErrorCode, ec.ErrorCode hexErrorCode,

    e.ErrorColumn,  ec.ErrorDescription, e.TableName, e.KeyValue

    from dwerrors e, dwerrorcodes ec

    where sys.fn_varbintohexstr(convert(varbinary,convert(int,e.ErrorCode))) = ec.ErrorCode

  10. Niall says:

    Awesome 🙂  Thanks for the help!

  11. lizzie says:

    Thanks for the post, i got what i need

  12. Michael says:

    Brilliant… It was months trying to decipher those nasty SSIS error messages…

Comments are closed.

Skip to main content