October 21, 2008 at 9:16 am
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.
March 5, 2009 at 12:16 pm
Did you ever get a resolution to this ? I'd like to know what you eventually have learned. Thx! Jim
March 6, 2009 at 6:56 am
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