Error Handling in SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jack Corbett » Error Handling in SSIS

105 posts, Page 2 of 11

««1 2 3 4 5 »»»

Error Handling in SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Leonard

Posted Monday, April 21, 2008 10:42 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375, Visits: 947

Hi Jack,

Great article!

:{> Andy

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard

Post #488105

craig.strong

Posted Monday, April 21, 2008 11:51 AM

Forum 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

Jack Corbett

Posted Monday, April 21, 2008 12:17 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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 (74 views, 93.57 KB)

Post #488155

Jack Corbett

Posted Monday, April 21, 2008 12:24 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #488161

craig.strong

Posted Monday, April 21, 2008 2:13 PM

Forum 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

Ben Sullins-437405

Posted Monday, April 21, 2008 2:28 PM

SSC 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

Jack Corbett

Posted Monday, April 21, 2008 7:03 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

Post #488303

Jack Corbett

Posted Wednesday, April 23, 2008 6:27 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Ben,

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

Post Attachments

ErrorReport.zip (29 views, 2.98 KB)

Post #489217

pradeep_panzade

Posted Wednesday, April 23, 2008 10:04 AM

Forum 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

Michael Jenck

Posted Wednesday, April 23, 2008 5:43 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 03, 2010 10:44 AM
Points: 13, Visits: 89

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 »

105 posts, Page 2 of 11

««1 2 3 4 5 »»»

Permissions