Error Handling in SSIS

  • Jack Corbett

    SSC Guru

    Points: 184360

    Comments posted to this topic are about the item Error Handling in SSIS


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Jack Corbett

    SSC Guru

    Points: 184360

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Andy Warren

    SSC Guru

    Points: 119675

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

  • Jack Corbett

    SSC Guru

    Points: 184360

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Anipaul

    SSC-Insane

    Points: 24681

    Hi Jack

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

    🙂

  • Grant Fritchey

    SSC Guru

    Points: 395417

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • steitelbaum

    SSC Eights!

    Points: 864

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

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • steitelbaum

    SSC Eights!

    Points: 864

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

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


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Andy Leonard

    SSCrazy Eights

    Points: 9865

    Hi Jack,

    Great article!

    :{> Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • craig.strong

    SSC Enthusiast

    Points: 134

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

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Jack Corbett

    SSC Guru

    Points: 184360

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • craig.strong

    SSC Enthusiast

    Points: 134

    Thanks for the quick response!

Viewing 15 posts - 1 through 15 (of 108 total)

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