SSIS Logging. Find the cause of a failure.

  • I have Logging enabled in my SSIS package.

    I only want to see what caused package to fail.

    So in "Configure SSIS Logs" window I only selected OnError event

    to minimize the number of rows.

    ('m actually not very experienced with SSIS logging. I am reading all articles about SSIS Logging on the internet now to decide what is the best solution)

    Anyway,

    When I do a test and the package fails on INSERT at OLE DB Destination step

    here is what gets logged in [sysdtsLog90] table:

    starttimemessageideventcomputeroperatorsource

    ---------------------------------------------------------------

    2008-10-21 10:33:37.000Beginning of package execution. 1PackageStartBNSLKZGH4WBNS\ROpeshanksyBRANCH2

    2008-10-21 10:33:38.000SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". 2OnError

    2008-10-21 10:33:38.000SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". 3OnError

    2008-10-21 10:33:38.000There was an error with input column "REGION_CD" (7978) on input "OLE DB Destination Input" (3423). The column status returned was: "The value violated the integrity constraints for the column.". 4OnError

    2008-10-21 10:33:38.000There was an error with input column "REGION_CD" (7978) on input "OLE DB Destination Input" (3423). The column status returned was: "The value violated the integrity constraints for the column.". 5OnError

    2008-10-21 10:33:38.000SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (3423)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (3423)" 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. 6OnError

    2008-10-21 10:33:38.000SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (3423)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (3423)" 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. 7OnError

    2008-10-21 10:33:38.000SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLEDST_Stage" (3410) 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. 8OnError

    2008-10-21 10:33:38.000SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLEDST_Stage" (3410) 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. 9OnError

    2008-10-21 10:33:38.000SSIS 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. 10OnError

    2008-10-21 10:33:38.000SSIS 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. 11OnError

    2008-10-21 10:33:38.000End of package execution. 12PackageEndBNSLKZGH4WBNS\ROpeshanksyBRANCH2

    But what I really need is one Error record:

    starttimemessageid

    ---------------------------

    2008-10-21 10:33:38.000There was an error with input column "REGION_CD" (7978) on input "OLE DB Destination Input" (3423). The column status returned was: "The value violated the integrity constraints for the column.". 4

    How to minimize the output?

    SSIS logs too many details that will confuse the operator

    and make it difficult to find the right record that

    explains the cause of a failure.

    I have screen shots attached.

  • Did you ever get a resolution to this ? I'd like to know what you eventually have learned. Thx! Jim

  • In SSIS Logging in Details Tab

    you have to select only the events you really need.

    In my case for example i checked "OnError" and "OnInformation".

    That's it.

    This reduces the output considerably.

    I actually don't even need "OnInformation" event enabled but

    if I disable it I SSIS stops writing to

    [dbo.sysdtsLog90] from my Script Task:

    Dts.Events.FireInformation(0, "", vErrCount + " records failed conversion check while converting [SRC_CURRENCY]", "", 0, True)

    which I need.

    I might play around a bit with other Dts.Events.XXXXXX events.

    I just don't have time for it. It's good enough for now.

Viewing 3 posts - 1 through 2 (of 2 total)

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