SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieving the Error Type in the Data Flow


Retrieving the Error Type in the Data Flow

Author
Message
tim.ffitch 25252
tim.ffitch 25252
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1425 Visits: 201
I have a flat file loading process that first loads to a staging table and then we process updates into the database from there. When and if this loads fail it is usual one of 2 things. Either there is a problem with the file itself or there is a database deadlock.

What I want to be able to do is intercept the error type in the data flow, if it is a deadlock then I want the package to error as usual and let the retries on the SQL Agent Job run as usual in the hope that the deadlock is gone on the subsequent retries. If the error is not a deadlock, then I want to move the file into a failed folder so the SQL Agent Retry will kick in and the next file in turn will be processed.

Has anyone got any ideas how I can intercept the error type to help me build this deign pattern.

Thanks
Tim.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214864 Visits: 24756
tim.ffitch 25252 - Tuesday, February 6, 2018 9:27 AM
I have a flat file loading process that first loads to a staging table and then we process updates into the database from there. When and if this loads fail it is usual one of 2 things. Either there is a problem with the file itself or there is a database deadlock.

What I want to be able to do is intercept the error type in the data flow, if it is a deadlock then I want the package to error as usual and let the retries on the SQL Agent Job run as usual in the hope that the deadlock is gone on the subsequent retries. If the error is not a deadlock, then I want to move the file into a failed folder so the SQL Agent Retry will kick in and the next file in turn will be processed.

Has anyone got any ideas how I can intercept the error type to help me build this deign pattern.

Thanks
Tim.

Which version of SSIS are you running? Things changed in 2016 to make this task easier.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
tim.ffitch 25252
tim.ffitch 25252
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1425 Visits: 201
Phil Parkin - Tuesday, February 6, 2018 11:29 AM
tim.ffitch 25252 - Tuesday, February 6, 2018 9:27 AM
I have a flat file loading process that first loads to a staging table and then we process updates into the database from there. When and if this loads fail it is usual one of 2 things. Either there is a problem with the file itself or there is a database deadlock.

What I want to be able to do is intercept the error type in the data flow, if it is a deadlock then I want the package to error as usual and let the retries on the SQL Agent Job run as usual in the hope that the deadlock is gone on the subsequent retries. If the error is not a deadlock, then I want to move the file into a failed folder so the SQL Agent Retry will kick in and the next file in turn will be processed.

Has anyone got any ideas how I can intercept the error type to help me build this deign pattern.

Thanks
Tim.

Which version of SSIS are you running? Things changed in 2016 to make this task easier.

Funnily enough it is 2016. I have just begun to experiment with the OnError events and using a script task to see what information I can obtain from the error.

What else do you have in mind?

Thanks
Tim.

Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214864 Visits: 24756
tim.ffitch 25252 - Wednesday, February 7, 2018 7:36 AM
Phil Parkin - Tuesday, February 6, 2018 11:29 AM

Which version of SSIS are you running? Things changed in 2016 to make this task easier.

Funnily enough it is 2016. I have just begun to experiment with the OnError events and using a script task to see what information I can obtain from the error.

What else do you have in mind?

Thanks
Tim.

I was referring to the use of a Script Component (not Task) to retrieve Error Column Name and Error Column Description, as described here. It might help, if you need to be very granular.
But I think that you are on the right track with your OnError research.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
tim.ffitch 25252
tim.ffitch 25252
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1425 Visits: 201
Phil Parkin - Wednesday, February 7, 2018 7:53 AM
tim.ffitch 25252 - Wednesday, February 7, 2018 7:36 AM
Phil Parkin - Tuesday, February 6, 2018 11:29 AM

Which version of SSIS are you running? Things changed in 2016 to make this task easier.

Funnily enough it is 2016. I have just begun to experiment with the OnError events and using a script task to see what information I can obtain from the error.

What else do you have in mind?

Thanks
Tim.

I was referring to the use of a Script Component (not Task) to retrieve Error Column Name and Error Column Description, as described here. It might help, if you need to be very granular.
But I think that you are on the right track with your OnError research.

Thanks for the info, I didn't know about that, it could prove very useful.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search