October 17, 2008 at 7:31 am
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.
October 17, 2008 at 7:39 am
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 Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 9:31 am
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?
October 17, 2008 at 9:46 am
Yes, almost every transform component has an error output.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 17, 2008 at 10:02 am
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
Ben Sullins
bensullins.com
Beer is my primary key...
October 17, 2008 at 10:11 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2008 at 2:43 pm
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?
October 20, 2008 at 2:51 pm
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 Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 21, 2008 at 7:10 am
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.
October 21, 2008 at 7:57 am
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.
October 21, 2008 at 7:59 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 21, 2008 at 8:42 am
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
October 21, 2008 at 9:30 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 21, 2008 at 11:39 am
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...
October 21, 2008 at 11:55 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 46 through 60 (of 107 total)
You must be logged in to reply to this topic. Login to reply