Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Error Handling in SSIS Expand / Collapse
Author
Message
Posted Monday, April 21, 2008 10:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:46 PM
Points: 388, Visits: 1,047
Hi Jack,

Great article!

:{> Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #488105
Posted Monday, April 21, 2008 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 1:22 PM
Points: 2, Visits: 26
Hi Jack,

At what point are you inserting the data into the table?

Where do you include the script component in your data flow?

Thanks,
Craig
Post #488136
Posted Monday, April 21, 2008 12:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
Craig,

You can use the script component as the error destination after any transformation (that has an error output) within the data flow. Then I immediately insert into the logging table.

So you can have a source then a lookup transform. In your lookup transform you set the error output to the script component then the script component output to an oledb datasource. Then you have a destination with an error output to a script component which has an output to an oledb destination. Attached is a screenshot of a data flow in my project which has 4 script components for error logging with 4 oledb destinations.

Hope this helps.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2


  Post Attachments 
DataFlowWithScriptComponents.JPG (78 views, 93.57 KB)
Post #488155
Posted Monday, April 21, 2008 12:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
Andy Leonard (4/21/2008)
Hi Jack,

Great article!

:{> Andy


Thanks Andy. Just read and commented on your blog post on Developer Communities: The Team Builder. It is a recommended read.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #488161
Posted Monday, April 21, 2008 2:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 1:22 PM
Points: 2, Visits: 26
Thanks for the quick response!
Post #488223
Posted Monday, April 21, 2008 2:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, Visits: 267
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!



Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...
Post #488231
Posted Monday, April 21, 2008 7:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #488303
Posted Wednesday, April 23, 2008 6:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
Ben,

Here is a basic SSRS report for the data in the load_errors table. Admittedly basic but it works.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2


  Post Attachments 
ErrorReport.zip (29 views, 2.98 KB)
Post #489217
Posted Wednesday, April 23, 2008 10:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 11, 2012 12:26 PM
Points: 3, Visits: 106
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
pradeepvpanzade@yahoo.com
Post #489400
Posted Wednesday, April 23, 2008 5:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:25 AM
Points: 13, Visits: 94
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
Post #489642
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse