Error Handling in SSIS

Question

Post reply

Error Handling in SSIS

Jack Corbett

SSC Guru

Points: 184394
More actions
April 20, 2008 at 7:26 am

#380073

Comments posted to this topic are about the item Error Handling in SSIS
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 - 1 through 15 (of 107 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Just a couple of notes on things I thought about after submitting the article:

1. The title could have been better. It's really handling or logging data errors in a dataflow task.

2. I learned about the Audit task which could be used to get the step and start time.

I hope the article is helpful and I am looking forward to the feedback I get on it.

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

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

Jack, good to see something written by you on SSC!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Andy,

Thanks, it was nice to have found something to interesting to write about and then to have it published.

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Hi Jack

The article was nice. It also pointed some good areas of error.

🙂

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 5

Nice article.

I'm not an SSIS expert, but I overheard our SSIS guru's discussing your article. They liked it. That's a good sign.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

steitelbaum SSC Eights! Points: 864 More actions · Answer 6

looking forward to reading the whole article but the scollable code is showing up in a very narrow control, too narrow to read the code. Doesnt look sizeable. What do I need to do?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Thanks for the nice comments thus far.

steitelbaum,

What browser are you using? I have used both Firefox 2.0.0.14 and IE 7.0.5730.11 and the code sections are wide and readable. Anyway to help out I am attaching the script component and create table sql to this post.

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

steitelbaum SSC Eights! Points: 864 More actions · Answer 8

thanks Jack, IE7 version 7.0.5730.11. Was finally able to see it correctly after typing 1st post and using the back arrow a couple of times to return to the article.

I was very disappointed this weekend to find how sparse the feedback is when using sqlagent's cmdexec option to execute an ssis pkg, and instructing sqlagent to log info to job history. Your article couldnt have come at a better time.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

Great, glad you got it figured out and that the article was helpful.

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

Andy Leonard SSChampion Points: 10144 More actions · Answer 10

Hi Jack,

Great article!

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

craig.strong SSC Enthusiast Points: 134 More actions · Answer 11

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

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
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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

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
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

craig.strong SSC Enthusiast Points: 134 More actions · Answer 14

craig.strong

SSC Enthusiast

Points: 134

April 21, 2008 at 2:13 pm

#805195

Thanks for the quick response!