Error Handling in SSIS

  • I figured it out how to read XML column.

    A question.

    So it looks like the whole purpose of this Error Script component

    is to get ErrorDesc value. Right?

    In my case it was Data Conversion component

    from STR to DT_DATE that failed.

    The value was "1801-01Z03".

    So in ErrorDesc column I got

    "An error occurred during computation of the expression."

    Is it descriptive enough? Not really.

    Can you quickly identify which exactly column and value caused the problem? No.

    Unfortunately ErrorDesc does not explain the error very well.

    I was hoping to see some "conversion to DT_DATE data type failed" type of error.

    I will do some negative testing, provide some bad data

    and see if ErrorDesc maybe in other cases is more helpful.

  • If you go through this thread I there is a post with a SSRS Report that displays the error data.

    You are right that this does not tell you which column has the error. Currently SSIS does not provide a way to find the column name from the ErrorColumn value. Based on research I have done you would have to query the dtsx file (the package is stored as xml) to find the column name. A bit more than I am interested in taking on as having the column values and the error description I can fairly quickly determine which column caused the error, like you have with the invalid date.

  • Jack,

    Is it possible to have Error Output columns at OLE DB Destination component?

    If after all Conversions and manipulations you finally get an INSERT failure

    can you capture that failure in [error_load] table?

  • Yes, almost every transform component has an error output.

  • Jack Corbett (9/24/2008)


    Ben,

    I'm not sure what you mean. The code is in the custom component gives a readable error, granted "the data violates integrity constraints" won't mean much to a user, but you could translate it to "there are duplicate values".

    What I am doing, and I think I posted the rdl on the thread, is create a report using the errors I have logged, which, while not perfect has helped me send reports to my users that tell them that a date or phone number is bad.

    Can you give me an idea what you are looking for to send your users? Like an example of the information you want to give them.

    Jack,

    Maybe I need to read the article again but it's the second part of your first paragraph there where you say "but you could translate it" is what I'm looking for. Instead of hard coding the translation of "Index out of bounds" to "There is probably an extra tab in your text file", coming up with a table to hold these errors. So far I've added a couple tables to our ETL configuration database and will repost with my results after seeing it work for a few.

    Cheers,

    Ben


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • Ben,

    I guess I also misread or misunderstood your post the first time through. I guess the issue I have is that I can get the error , "The Data violates the Integrity Contraints" because I have duplicate key values or a foreign key value incorrect so how would I translate that message to my users without first reviewing the data? I'm definitely interested in seeing what you have come up with though.

  • Hi Jack,

    I have problems with using your Error Script.

    It's all working fine when I have:

    1. Records Row1,Row2,Row3 all fail conversion (all is logged nicely into load_errors)

    2. Records Row1,Row2 fail conversion and Row3 is successfully inserted into a database

    But..

    When I change Row3 on purpose so that it fails INSERT

    then Row1,Row2 stop being redirected to your ErrorScript.

    An it only logs "...violated integrity constraint.."

    No conversion errors.

    Strange enough Row1 and Row2 do not get inserted into a database either

    but they also do not get logged anywhere.

    It is like CONSTRAINT VIOLATION takes precedence

    and pipeline to ErrorScript is not available anymore

    Did anybody do this kind of test?

  • I'm not sure what you are trying to do.

    If the transformation or source has and error and you have configured the error output to redirect row the row SHOULD be redirected to the error component and then to the error logging destination. If the error is set to ignore error then everything will continue, if set to fail component then the package will fail on the error. Is the package failing?

    Can you attach your package to the thread or at least a screen shot of your flow so we can see what you are doing? Your explanation leaves something to be desired.

  • Jack,

    I've attached my BRANCH2.DTSX file

    together with a screen shot.

    "DCNV_1" Data Conversion task is where rows being redirected on error.

    As you can see 3 records arrive at "DCNV_1" step.

    1 record is being passed to OLE DB Destination.

    The other 2 records don't get redirected to "GetErrorDetails" script.

    These 2 records are my problem.

    As soon as I allow INSERT without errors everything starts working OK.

    2 records get redirected to "GetErrorDetails". 1 record gets inserted into a database.

    Please have a look.

    Thanks.

  • Jack,

    I hope I found a way around.

    The fact OLE DB Constraint Violation error sort of

    hijacked the buffer pushed me to this idea.

    Instead of connecting "DCNV_1" and OLE DB Destination

    I inserted one more task between them - "Union All".

    It's a dummy one, I don't need it. But it helps to

    slow down OLE DB Destination a little bit and allow to

    route all the records correctly.

    See the screen shots attached.

    Now I get Conversion Error details in [load_errors] table

    plus I catch OLE DB Constraint Violation error in my [sysdtsLog90] table.

    (I have Logging enabled for this package)

    That's actually was my goal - to capture as many errors as possible

    including all Lookup,Conversion and OLE DB Errors and store them in a SQL table.

    Right now I have them in two places ([error_load] and [sysdtsLog90] tables but I will try to consolidate them all later)

    I'm not sure if you like this little fix to your solution but at least

    in my case it resolves all my problems.

    Thanks.

  • Everything appears to be working as expected.

    In your image the data conversion is successfully processing all 3 rows, none caused an error, so there are no rows being passed to the error handling component.

    When the rows are passed to the destination, the first row being processed (and this may vary unless you are specifically providing an order by somewhere) is causing an constraint violation so it fails the package because you have no error handling configured on the destination. Remember that SSIS does Row by Row processing, so rows 2 and 3 in this case are never processed. Have you verified that the table is being truncated? Odds are it is not, so you are getting duplicate key value.

    You can change from Table or View - Fast Load to Table or View on your destination and then redirect error rows to an error component and log those error rows as well.

    I really recommend you download the custom component I wrote to encapsulate the error script. It is attached to a post earlier in this thread or available here: http://cid-d959306fcfdbac21.skydrive.live.com/self.aspx/Public/SSIS%20Components/JDCSSISLogging.zip. It does a better job of cleaning up the text and is re-usable. You can also extend it/change it as the source is included as well. I also blogged about it and linked to it and some resources for custom components here.

  • Hi Jack,

    In your image the data conversion is successfully processing all 3 rows, none caused an error, so there are no rows being passed to the error handling component.....

    Data conversion task was not processing all 3 rows properly.

    2 rows were stuck and not redirected to Error Script.

    Only when I added Union All between "DCNV_1" and OLE DB Destination

    it started to process all 3 rows correctly.

    Compare for yourself.

    --has a problem

    http://www.sqlservercentral.com/Forums/Attachment1629.aspx

    --problem is solved by adding Union All

    http://www.sqlservercentral.com/Forums/Attachment1631.aspx

  • I also mentioned that SSIS processes data row by row, it just looks like it is processing batches when it moves quickly. With this "pipeline" approach, an error down the line stops everything so in your case the first row was being inserted BEFORE the second row was being converted. The UNION ALL Transformation has to get ALL the rows before it can do it's part. If you are processing thousands or millions of rows adding the UNION ALL will have a severely negative affect on performance, whereas changing the destination to use Table or View and configuring error output will have less of an impact on performance.

    In both cases you show SSIS is performing as designed. it might not be the the behavior you desire/expect, but it is the designed behavior. If you plug a pipe at the second "T" and start filling it eventually the water backs up all the way to the top and you have to turn off the water. That's what SSIS is doing, when it gets blocked down the line the flow is turned off.

  • Hi Jack,

    ..., whereas changing the destination to use Table or View and configuring error output will have less of an impact on performance...

    Correct me if I'm wrong.

    So I should never have any task that FAILS the package on error?

    I always should have some Error Output

    to allow other tasks to finish?

    But if this is right then

    can I have just a dummy Error Output for OLE DB Destination step?

    I mean SSIS Logging records INSERT error in [sysdtsLog90] table

    and I'm happy enough with it.

    I just need to allow other tasks to finish...

  • The answer is "It Depends".

    In the project I was working on when I put this together I wanted a way to find out what data was causing my failures or, in the case of lookups, null values. In order to do that I needed to find a way to "log" the data. Not really being an SSIS expert I noticed the error output and decided that since this is not a nightly load I don't care if partial data gets inserted because I am still "testing" and I want to know what data did not get loaded. Now, I have not tried this, but you may be able to accomplish a similar thing by setting the OnError event, logging the data, and then have the package still fail, but I don't think you'd have a way of getting the exact row that failed. I needed to know that ID 72 had an invalid date so I could get users in the existing system to fix the invalid data.

    If I was loading a data warehouse or database where it would be better to have no data then I'd fail the package on error and send myself an email, page, etc... so I'd know it needs fixing.

Viewing 15 posts - 46 through 60 (of 107 total)

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