Error Handling in SSIS

  • Hi Jack,

    I didn't invent any new Error Script components.

    I used the same your Script.

    And the results are good. I get all the errors logged.



    12008-10-21 13:59:37.000The data value violates integrity constraints.

    22008-10-21 13:59:37.000The data value cannot be converted ...

    32008-10-21 13:59:37.000The data value cannot be converted ...

    There is only one little inconvenience for me.

    I just realized that without having "FAIL package on Error"

    my SSIS will never stop executing which is undesirable.

    Can I stop execution of a package if an error occurred at "OLE DB Destination" step? After my OLE DB Destination--->Error Output Script

    I'd like to have another Email Notification step and then I'd like to stop the execution of the package.

    Here is my desired flow:

    OLE DB Destination--->Error Output Script--->EmailNotification--->Stop execution

  • I realize you did not create a new component, what I am saying is that I created a Custom Component for SSIS that you can add to the toolbox and drag and drop into your packages. With the script component you have to copy and past the script multiple times, etc...

    To do what you want done, I would think you could populate a variable, say ErrorCount, increment it in your script, then after your data completes you would have 2 paths using Expression and Constraint as your type and if the ErrorCount variable > 0 go to an Email step and end the package and the other path would be if ErrorCOuntv = 0 continue package.

    You need to realize that implementing it this way will still populate your destination tables, just without the rows you redirected.

  • Jack,

    My boss doesn't want me to build any SSIS packages based on

    custom components. I personally would definitely give it a try but

    he's the boss...

    So everything looks good now except that my SSIS Logging stopped reporting any "OnError" events. I think all "OnError" events are now being intercepted by your Script.

    That means I don't have a choice. I will have to turn off SSIS Logging

    and completely switch to Error Script and rely on it. I liked SSIS Logging.

    It was automated and very detailed. It was very convenient to monitor the progress of the package. For example, I used OnPipelineRowsSent event

    and it reported how many rows were on each input and output for each task. Was kind of convenient.

    But now it becomes incomplete. OnError event does not get logged.

    So my plan now is to change [load_errors] table:

    Rename it to "Messages"

    Add "message" column and log package progress together with any errors that occur.

    Like "Package BRANCH2.dtsx started" for example.

    Or "214 records were processed"

  • Your boss is being a little shortsighted as custom components can save a ton of time. You should still download mine for the source as it does a better job and you can adapt that to us in your script component.

  • Hi Jack,

    I tried to increment vGlobalErrorCount in ErrorDetails Script step

    Variables.vGlobalErrorCount = 1

    but got an error:

    The collection of variables locked for read and write access is not available outside of PostExecute

  • This worked...

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub PostExecute()

    Variables.vGlobalErrorCount = 1

    'MsgBox("vGlobalErrorCount=" + vGlobalErrorCount.ToString)

    End Sub

    End Class

    Now in my Control Flow I can check for vGlobalErrorCount

    and Send an email if there was an INSERT error.

  • Hi Jack,

    So how about that little bug?

    My output column has


    value and insert into [load_errors] table fails with this error:

    Error: 0xC0202009 at DFT_branch, OLE DB Destination [5537]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "XML parsing: line 1, character 80, illegal name character".

    Information: 0x402090E0 at DFT_branch, OLE DB Destination [5537]: The final commit for the data insertion has ended.

    As soon as I remove " & " character from "MAIN & RUPERT, WINNIPEG, MAN."

    insert works.

    Is it a bug?

    What should I do with "&" character?

  • Here is what I found on MSDN about "&" character...

    With XML the ampersand needs to be escaped as & a m p ; otherwise your data is not well-formed and any XML parser will reject it.

    I guess I need to replace "&" in Error Script

    but not sure how to do it. Anybody?

  • This is why I keep asking you to go get the Custom Component I posted. I included the source code which fixes the problem with illegal characters. I don't have the code in front of me now, but if you go download it you will have it.

  • Jack,

    I tried to open your "JDC.SSIS.Logging.sln"

    in my Visual Studio 2005

    and got this error:

    "The selected file is a solution file but was created by a newer version of this application and can not be opened."

    Is your project compatible with VS2005?

    I'm new to Custom Components.

    How do I install your Component in my Business Intelligence Development Studio?

  • Nope I did it in VS 2008, but I thought I had the compatability mode in 2005.


  • Thanks for reply Jack.

    I guess then I need to wait until the company converts to VS 2008...

    Is there like an Express version of VS 2008 free of charge?

  • Yes there is C# Express and VB Express 2008 available.

  • Jack,

    Will I be able to build your component using that Express C# or VB.NET VS2008? If I compile it using VS2008 Express will I be able to use that component in my BI Development Studio 2005?


    Can you give us the instructions on how to build a component?

    I tried to follow the instructions from


    and it is kind of hard.

    I'm new to this. I've never done a custom component in SSIS.

  • I have installed/upgraded your Logging component to 08. Got it working - pretty sweet.

    I am adding some enhancements and upgraded to 2008 - do you want the source?


Viewing 15 posts - 61 through 75 (of 107 total)

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