Handling Multiple Errors in SSIS

[edited on 12/14/2007 to correct an error in the text around string handling - the samples were not modified]
One actual failure in SSIS can trigger a whole series of error messages. For example, failure to convert a column value from a string to an integer in a Derived Column transform generates the following messages:
[Data Conversion [70]] Error: Data conversion failed while converting column “Fiscal year” (18) to column “NumericFiscalYear” (83). The conversion returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
[Data Conversion [70]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “output column “NumericFiscalYear” (83)” failed because error code 0xC020907F occurred, and the error row disposition on “output column “NumericFiscalYear” (83)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Data Conversion” (70) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread “WorkThread0″ has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
If you are logging errors to a flat file or an error logging table, then recording each error may be fine. However, if you’re writing the errors to the Windows event log, or sending them via email, you may not want to record multiple messages each time an error occurs. You might want to record only the first message, or you might want to group all the errors into a single log entry or email. Fortunately, the event model in SSIS allows you to easily customize how errors are handled.
I’ve put together a small sample package that shows how you might accomplish this. The package contains a single data flow that loads a text file, attempts to convert a column from string to numeric, and writes it to a Trash destination (see www.SQLIS.comΒ to get this component).
The text file has an invalid value in one of the columns, which will cause the data flow to fail, and generate the four messages listed above. The package is set up to capture all of the error messages generated, store them in a collection, and concatenate them into a single string when the package is finished executing. Once that is done, the resulting string could be emailed or recorded to a log.
As mentioned, the data flow is very straightforward:
Β
I’ve also created two variables at the package level: errorMessages as an Object, and emailText as a String. I’ll explain why later in the post.
The real work occurs in the event handlers. SSIS raises events for all executables(packages and tasks are both executables). The event we’re interested in is the OnError event, which is raised once for each error that occurs.
You get to the event handlers by selecting the Event Handlers tab in the SSIS designer. Once there, the Executable for which you want to capture events needs to be selected.

Since I want to handle errors for anything in the package, I’m setting the executable to CaptureErrors (the name of theΒ package). By default, any event raised by a child executable (that is, an executable that is nested inside another executable) will also be raised in its parent. You can disable that behavior by setting the Propagate system variable, but that’s a topic for another post. I’m also using “OnError” from the list of events and have added a Script Task to the event handler.

The Script Task has two variables passed in: the System::ErrorDescription, which contains the text of the error message, and User:errorMessages, which I’ll use to keep track of all the error messages.

Here’s the script used in the Script Task:
Dim messages As Collections.ArrayList
Try
Β Β Β  messages = CType(Dts.Variables(“errorMessages”).Value, Collections.ArrayList)
Catch ex As Exception
Β Β Β  messages = New Collections.ArrayList()
End Try

messages.Add(Dts.Variables(“ErrorDescription”).Value.ToString())
Dts.Variables(“errorMessages”).Value = messages
Dts.TaskResult = Dts.Results.Success
I’m first attempting to retrieve the ArrayList from the errorMessages variable. If the value of the variable can’t be cast to an ArrayList, it indicates that it hasn’t been initialized yet. Once that has been handled, the error description is added to the ArrayList. This handles capturing the list of all error messages.
The next step is to process all the messages in order to email or log them. Since I only want to do this once, I’m using the OnPostExecute event, which fires when the executable is finished running.

There is another Script Task present in this event handler. This one has the User::errorMessages and User:emailText variables passed in.

The script in this task is concatenating a long string based on the error messages captured and returning it in the emailText variable:
Dim errorDesc As String
Dim messages As Collections.ArrayList

Try
Β Β Β  messages = CType(Dts.Variables(“errorMessages”).Value, Collections.ArrayList)
Catch ex As Exception
Β Β Β  ‘If there is an exception – the object was never initialized, so there were no errors
Β Β Β  Return
End Try

For Each errorDesc In messages
Β Β Β  Dts.Variables(“emailText”).Value = Dts.Variables(“emailText”).Value.ToString + errorDesc + vbCrLf
Next

Dts.TaskResult = Dts.Results.Success
Once that has been done, the resulting string could be emailed or logged as desired. Since SSIS can generate fairly verbose error messages, I chose to store the messages in an ArrayList object. I then use the ArrayList to build the actual string message.
Hopefully, this information is helpful to anyone who wants to customize SSIS event handling. I’ve attached the sample package, and the text file used in the sample. If you have any feedback or suggestions for improvement, please leave them in the comments.

43 Comments

  1. http:// says:

    Hi,

    Fist of all sorry for my English.

    I’ve got a question…

    I’m using your sample code to get the errors, but I like that the execution of my data flow doesn’t stop when there is an error.

    I want to use your code to make a log file but I want also that my data flow continue like if I chose “Ignore errors”

    Can you help me?

  2. jwelch says:

    If you want to log data errors from the data flow, use Redirect Rows instead of Ignore Errors. If you use Ignore Error, no error is raised so you can’t use error handlers to log them.

    Redirect Row will send the error row to a different output so you can send it to a seperate destination.

  3. http:// says:

    System::ErrorDescription does not appear to exist! I don’t see it in the package explorer, and calling it from the script task leaves this error:

    Error: Failed to lock variable “ErrorDescription” for read access with error 0xC0010001 “The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.”.

  4. http:// says:

    In regards to my last comment, as soon as I posted it, I realized that I was using the OnTaskFailed event, instead of the OnError event. When I moved my code into the OnError event, everything worked. So it looks like System::ErrorDescription must only exist in that one event.

  5. http:// says:

    Hi,
    In the given example, you have helped us to know how to concatenate all the error message into a single string, many thanks for that. But, Is there any way or option to fetch the first error message alone to be captured and logged?

    Thanks Much,
    Arvind

  6. jwelch says:

    Sure, you could just set a flag after the first error message, and not add any more messages. If you wanted to get a little fancier, you could track the time of the event, and only add new messages if they occurred more than a specified interval after the first one.

  7. http:// says:

    Hi,
    I really liked this idea and have used this when sending error emails. However, I seem to generate two of every error message in the email which i cant understand.

    Thanks for your help,
    Scott.

  8. jwelch says:

    Do you have the script task in two error handlers (maybe one at the package and one at the data flow level)?

  9. http:// says:

    Perfect!

    Thankyou!

  10. http:// says:

    Thanks so much for this post. Perfect example of what i needed to do.

    I am also receiving two emails per error. I do not have any data flow in my package. Was using the Explorer to try and see if i have doubled the script task but did not find any.

    Is it because i am propagating?

  11. jwelch says:

    Propagation might cause it, but I wouldn’t think so. Your script task in the error handler should just be storing the error message, not email it. The email should be sent from the OnPostExecute if you are putting it on the task that you expect to generate the error. If not, just send the email from the main control flow after the other tasks finish executing.

  12. apulxb says:

    Thank you for your work! It gave me a lot of help. But because I found in my case when a error happened in the grandchild package, the error message would show multiple times in the letter, it seems that the times repeated depend on how many levels among your packages are in the project. I often put one Execute Package Task in another Execute Package Task, I think that the error information is transferred from the error level to the highest level, so we get the repeated info.
    Could you tell me how to avoid the repeated Error Info with the Vb.net code? thank you!

  13. http:// says:

    Hello,
    I’ve downloaded the file and created the variables accordingly but I’m still getting the following error:
    Error: Failed to lock variable “User:errorMessages” for read access with error 0xC0010001 “The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.

  14. http:// says:

    Please disregard the above post,

    I only had one “:” (User:errorMessages)

    It should be (User::errorMessages)

    Now I just need to figure out how to send the email. Thanks, Jeff

  15. http:// says:

    What about erros that occur in the initial package validation? Those don’t seem to be caught by OnError. Any suggestions?

  16. http:// says:

    very good article indeed. Helped me a lot. I made some ajustments to the script for recording the error messages:

    Dim messages As Collections.ArrayList

    ‘ First attempting to retrieve the ArrayList from the errorMessages variable. If the value of the variable can’t be cast to an ArrayList,
    ‘ it indicates that it hasn’t been initialized yet. Once that has been handled, the error description is added to the ArrayList.
    ‘ This handles capturing the list of all error messages.
    Try
    messages = CType(Dts.Variables(“custerErrorMessages”).Value, Collections.ArrayList)
    Catch ex As Exception
    messages = New Collections.ArrayList()
    messages.Add(“───────────────────────────────────────────────────────────────────────────────────────────────────────────────────”)
    messages.Add(Dts.Variables(“ContainerStartTime”).Value.ToString() + ” — ” + Dts.Variables(“PackageName”).Value.ToString())
    messages.Add(“───────────────────────────────────────────────────────────────────────────────────────────────────────────────────”)
    messages.Add(“”)
    End Try

    If Dts.Variables(“custerrCurrentSourceName”).Value.ToString() <> Dts.Variables(“SourceName”).Value.ToString() Then
    messages.Add(“”)
    messages.Add(Dts.Variables(“SourceName”).Value.ToString())
    messages.Add(“────────────────────────────────────────────────────”)
    Dts.Variables(“custerrCurrentSourceName”).Value = Dts.Variables(“SourceName”).Value.ToString()
    End If
    messages.Add(“–> ” + Dts.Variables(“ErrorDescription”).Value.ToString() + ” (errorcode: ” + Dts.Variables(“ErrorCode”).Value.ToString() + “)”)

    Dts.Variables(“custerErrorMessages”).Value = messages

    Dts.TaskResult = Dts.Results.Success

  17. http:// says:

    Great post I do have one question.

    I have a script task that is ftping files via code. When the connection manager failes to connect to ftp server the error that is reported to my onerror event handler is script task returned a failure. Is there a way to report the actual error the connection manager throwed.

  18. Arturo says:

    Hi John,

    I’m sorry to come with a complain. I can’t see any image neither the link to download the code.

    Thanks,
    Arturo

  19. sri says:

    Hi, how do i write this line in C#? Thanks

    messages = CType(Dts.Variables(β€œerrorMessages”).Value, Collections.ArrayList)

  20. BavaBeesElofe says:

    just examined the thread. Amazing work.

  21. warren says:

    Thank you so much sir.
    This is exactly what i was looking for and it works flawlessly. big ups

  22. Rajesh says:

    Hi, Very good example. Even I was serching solution for the same problem. Thank you very much for the solution… :)

  23. Terri says:

    Thank you, this was exactly what I needed to do, and this was extremely helpful. A question: I have multiple data flows inside a container that the messages are being collected for. When the errors are reported, is there a way to include the System::TaskName along with the error messages that are being sent in the email?

  24. Terri says:

    Never mind, since I asked the question I figured out how to add what I needed. Thanks again for this very helpful post.

  25. Sintia Angarita says:

    Implemented the solution on the C# SSIS Project. I get the email to be sent, but the variable that is suppose to populate the body of the email is blank every time. What am I missing?

  26. Sam says:

    Hi,
    I have done exactly what is mentioned in this post. It works but it generates more than one emails. In my case it’s generating three emails. Can i have the surce code of your link?

    I need it badly and stuck.

    Hope to hear you soon

  27. Sam says:

    It generates more than one emails. Three in my case. Can i have the source code of your solution or Can you please suggest what might be the cause of this.

  28. JordanDulwich says:

    Fantastic, used this in project at work to prove our ssis packages. I added a little extra code just pull out those error messages that can be understood by non technical people. In this instances I was emailing errors returned by csv files that were not formatted correctly, to cut out all the technical stuff I used

    For Each errorDesc In messages
    If Not errorDesc.StartsWith(“SSIS”) Then
    concat = concat + ” ” + vbCrLf + errorDesc + vbCrLf
    End If
    Next

    The messages were then emailed to end users who were managers who needed to know why intregration had failed. ie:

    The integration that started at 019/07/2012 16:41:16 generated the following error(s):

    Data conversion failed. The data conversion for column “StartDate” returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.

    The “output column “StartDate” (1080)” failed because error code 0xC0209084 occurred, and the error row disposition on “output column “StartDate” (1080)” specifies failure on error. An error occurred on the specified object of the specified component.

    An error occurred while processing file “T:\SSIS\Universal\SBOOKING.csv” on data row 14.

    An error occurred while processing file “T:\SSIS\Universal\SEVENT.csv” on data row 1.

  29. Ravi says:

    Thx for such a wonderful and elegant way to send error emails.

  30. Faiz says:

    Use the Expression task in 2012 and make your life simple.

    @[User::ErrorMessage] = @[User::ErrorMessage] +”
    “+(DT_WSTR, 50) @[System::ErrorCode] +”: “+ @[System::ErrorDescription]

  31. [...] The post details the steps necessary and gives a great deal of insight into errors within SSIS, http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/, Thank youJohn [...]

  32. Tami says:

    Thank you very helpful!

  33. sree says:

    I implemented code to capture error messages into a single message but my script task is not stoppping…What could be reason

  34. bp says:

    You rock!!
    Thanks for posting.

  35. Bill says:

    Thanks. This really helped. Fairly straight forward and it worked like a charm.

Leave a Reply