April 21, 2008 at 2:28 pm
Jack,
Nice article...Being more of a front end guy I was wondering your thoughts on taking your query you use to look at the SSIS errors and converting that into a simple SSRS report. Could be packaged together as a report pack or something...
Thanks for the content!
Ben Sullins
bensullins.com
Beer is my primary key...
April 21, 2008 at 7:03 pm
Ben,
Thanks for the feedback. I had never really thought of using SSRS on the front of the query. I actually am not comfortable enough with XML know how to best query the XML column, but with SSRS I could probably use a Matrix on the Name-Value pairs. I'll have to look into some more. If you have any ideas let me know. It might make a nice follow-up article.:cool:
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
April 23, 2008 at 6:27 am
Ben,
Here is a basic SSRS report for the data in the load_errors table. Admittedly basic but it works.
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
April 23, 2008 at 10:04 am
Hi,
Can anyone give me the code for error handling. I am trying to transfer records from excel to SQL table. I want error records to be inserted into one error table with same scheme as orignal table.
I have tried all the solutions, but I am not getting it.
Please help me out... its urgent..
Thanks,
Pradeep
April 23, 2008 at 5:43 pm
Hi Jack,
Great article. I am receiving 2 errors and I'm not sure why
Row.ErrorSource is not a member of the component
Row.ErrorDetails is not a member of the component
Any thoughts on what I am doing wrong?
Mike
April 23, 2008 at 9:02 pm
Michael,
Thanks for the nice words.
The issue you are having is that you have not created the 2 columns in the Input and Output screen in the Script Transformation Editor. Step 2 in the article. If you do not add the columns to the output you can't reference them. All you have on the row are the input columns.
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
April 23, 2008 at 10:16 pm
In Step 2
a. ErrorStep string(100) – this is going to be the Name of the Script Component which I name using the step which is the source of the error
b. ErrorDataDetails Unicode String(4000) – this is going to be an XML string that will have the data from the Input Columns and will be inserted into an XML Type column in SQL Server
c. ErrorDesc string(100) – The error description gotten from the ErrorCode
d. ErrorTask string(100) – The SSIS Task that the Step is in. In my case it is a DataFlow Task. This is retrieved using the TaskName package variable that is created by SSIS.
e. PackageTime Date – The start time of the package. This is retrieved using the StartTime package variable.
So do I add them top the output?
April 23, 2008 at 11:12 pm
I think I figured out the problem
ErrorDataDetails should be ErrorDetails
I did have to add ErrorSource ( I guessed at 100 characters)
Mike
April 24, 2008 at 6:06 am
Looks good.
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
April 27, 2008 at 11:27 am
Hi,
Can u please send me one example explaining the error output to SQL Table.
Pradeep
April 28, 2008 at 7:30 am
pradeep_panzade (4/27/2008)
Hi,Can u please send me one example explaining the error output to SQL Table.
Pradeep
I think I see what you are asking for now. I never mention in the article that you need an OLEDB Destination (or SQL Server destination) to send the error output to. So what you need to do is add the Error Output Destination (OLEDB Destination or SQL Server Destination) to your data flow and then send the output of the script component to the Error Output destination and then it maps just like any other source -> Destination pair.
Sorry, in the article I assumed that everyone would know that you need to create the destination. A fairly serious omission on my part.
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
May 12, 2008 at 6:02 pm
Very interesting article. It's excellent.
I do have a question: In first screen shot under
"1. On the Input screen add the columns that could be the source of the error"
Do your input comes from the Success (green) data path or the error (red) data path? When I choose the input from the success data path I only see input columns but not error columns. On the other hand, when I choose input from the error path, I only see the error column but not the input columns. How do you get all columns to show in the Script Editor Input tab?
May 13, 2008 at 6:30 am
You use the Error data path. The only thing I can think of is that you need to Configure Error Output for the Transformation task and set it to redirect row.
Did you select "Transformation" as the type for the script component?
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
May 13, 2008 at 12:40 pm
Jack,
Thanks for your response. It helped me looked a little deeper and found the issue. I would like to share the information for those who may run into similar problem.
My problem was I followed the reference to Jamie Thomson article and use the Flat File Source as the output to the script transformation component. Apparently, the ErrorOutput from the Flat File Source only show three available input columns, namely the "Flat File Source Error Output Column", the "ErrorCode" column, and the "ErrorColumn". To have these three columns AND the other input columns to show up we need to feed the Flat File Source input into Lookup components (as shown in your screenshot) then get the ErrorOutput from the Lookup component and feed them into the Script Component.
Again, excellent article Jack. I'm interested in using your idea to create a custom component out of it so the community can make use of your idea without needing to copy/paste the script in multiple places. I think this would be useful until Microsoft improve SSIS error debugging and reporting process. I will definitely credit you for your work in any future articles that I might come up with relating to this topic. Please let me know what you think and we can communicate further via email. Thank you.
Kelvin Ninh
May 13, 2008 at 1:15 pm
Kelvin,
Thanks for the positive feedback.
Have fun with creating the custom component. I have considered it, but I am not confident/comfortable in .NET and have not found a custom component example I thought I could base this off of. All I want is a copy of the custom component when done.
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 - 16 through 30 (of 107 total)
You must be logged in to reply to this topic. Login to reply