Error Handling in SSIS

  • Jack,

    Nice article...Being more of a front end guy I was wondering your thoughts on taking your query you use to look at the SSIS errors and converting that into a simple SSRS report. Could be packaged together as a report pack or something...

    Thanks for the content!


    Ben Sullins
    Beer is my primary key...

  • Ben,

    Thanks for the feedback. I had never really thought of using SSRS on the front of the query. I actually am not comfortable enough with XML know how to best query the XML column, but with SSRS I could probably use a Matrix on the Name-Value pairs. I'll have to look into some more. If you have any ideas let me know. It might make a nice follow-up article.:cool:

  • Ben,

    Here is a basic SSRS report for the data in the load_errors table. Admittedly basic but it works.

  • Hi,

    Can anyone give me the code for error handling. I am trying to transfer records from excel to SQL table. I want error records to be inserted into one error table with same scheme as orignal table.

    I have tried all the solutions, but I am not getting it.

    Please help me out... its urgent..



  • Hi Jack,

    Great article. I am receiving 2 errors and I'm not sure why

    Row.ErrorSource is not a member of the component

    Row.ErrorDetails is not a member of the component

    Any thoughts on what I am doing wrong?


  • Michael,

    Thanks for the nice words.

    The issue you are having is that you have not created the 2 columns in the Input and Output screen in the Script Transformation Editor. Step 2 in the article. If you do not add the columns to the output you can't reference them. All you have on the row are the input columns.

  • In Step 2

    a. ErrorStep string(100) – this is going to be the Name of the Script Component which I name using the step which is the source of the error

    b. ErrorDataDetails Unicode String(4000) – this is going to be an XML string that will have the data from the Input Columns and will be inserted into an XML Type column in SQL Server

    c. ErrorDesc string(100) – The error description gotten from the ErrorCode

    d. ErrorTask string(100) – The SSIS Task that the Step is in. In my case it is a DataFlow Task. This is retrieved using the TaskName package variable that is created by SSIS.

    e. PackageTime Date – The start time of the package. This is retrieved using the StartTime package variable.

    So do I add them top the output?

  • I think I figured out the problem

    ErrorDataDetails should be ErrorDetails

    I did have to add ErrorSource ( I guessed at 100 characters)


  • Looks good.

  • Hi,

    Can u please send me one example explaining the error output to SQL Table.


  • pradeep_panzade (4/27/2008)


    Can u please send me one example explaining the error output to SQL Table.


    I think I see what you are asking for now. I never mention in the article that you need an OLEDB Destination (or SQL Server destination) to send the error output to. So what you need to do is add the Error Output Destination (OLEDB Destination or SQL Server Destination) to your data flow and then send the output of the script component to the Error Output destination and then it maps just like any other source -> Destination pair.

    Sorry, in the article I assumed that everyone would know that you need to create the destination. A fairly serious omission on my part.

  • Very interesting article. It's excellent.

    I do have a question: In first screen shot under

    "1. On the Input screen add the columns that could be the source of the error"

    Do your input comes from the Success (green) data path or the error (red) data path? When I choose the input from the success data path I only see input columns but not error columns. On the other hand, when I choose input from the error path, I only see the error column but not the input columns. How do you get all columns to show in the Script Editor Input tab?

  • You use the Error data path. The only thing I can think of is that you need to Configure Error Output for the Transformation task and set it to redirect row.

    Did you select "Transformation" as the type for the script component?

  • Jack,

    Thanks for your response. It helped me looked a little deeper and found the issue. I would like to share the information for those who may run into similar problem.

    My problem was I followed the reference to Jamie Thomson article and use the Flat File Source as the output to the script transformation component. Apparently, the ErrorOutput from the Flat File Source only show three available input columns, namely the "Flat File Source Error Output Column", the "ErrorCode" column, and the "ErrorColumn". To have these three columns AND the other input columns to show up we need to feed the Flat File Source input into Lookup components (as shown in your screenshot) then get the ErrorOutput from the Lookup component and feed them into the Script Component.

    Again, excellent article Jack. I'm interested in using your idea to create a custom component out of it so the community can make use of your idea without needing to copy/paste the script in multiple places. I think this would be useful until Microsoft improve SSIS error debugging and reporting process. I will definitely credit you for your work in any future articles that I might come up with relating to this topic. Please let me know what you think and we can communicate further via email. Thank you.

    Kelvin Ninh

  • Kelvin,

    Thanks for the positive feedback.

    Have fun with creating the custom component. I have considered it, but I am not confident/comfortable in .NET and have not found a custom component example I thought I could base this off of. All I want is a copy of the custom component when done.

Viewing 15 posts - 16 through 30 (of 107 total)

You must be logged in to reply to this topic. Login to reply