March 8, 2017 at 12:32 pm
Hi,
I have an SSIS package with an Execute SQL task which executes a proc and inserts rows in SQL table. In case of failure of this task, I need to count the error rows and redirect the error rows to a file . If error rows count < 10, then I have to continue the execution of the package and if it is more I need to fail the package.
I know how to do that inside the DataFlow task and not sure how to handle it inside the Execute SQL task. Any help is appreciated.
Thanks in advance.
March 10, 2017 at 3:00 pm
Variables and precedent constraints.
After your stored procedure runs add another Execute SQL Script to return the number of rows in the error table.
If it's 0 then run down the success path.
If it's greater than 10 run it down the failure path.
March 15, 2017 at 7:54 pm
Hi JustMarie,
Thanks for the suggestion. I have a proc that has the sql which fetches data from different tables and inserts into destination table. I was not able to redirect rows from SQL task. So, I redesigned my package to use dataflow task. I am able to redirect error rows to a different table. I am getting error number and error column but not the error description. I am not sure how to get the error description. My package is still work in progress. Please let me if you happen to know to get the error description with the error number.
Thanks for all your help!
March 16, 2017 at 6:33 am
Saig1417 - Wednesday, March 15, 2017 7:54 PMHi JustMarie,
Thanks for the suggestion. I have a proc that has the sql which fetches data from different tables and inserts into destination table. I was not able to redirect rows from SQL task. So, I redesigned my package to use dataflow task. I am able to redirect error rows to a different table. I am getting error number and error column but not the error description. I am not sure how to get the error description. My package is still work in progress. Please let me if you happen to know to get the error description with the error number.Thanks for all your help!
Are you using 2016? If so, it's not difficult. If not, it is difficult!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 16, 2017 at 9:04 am
Phil Parkin - Thursday, March 16, 2017 6:33 AMSaig1417 - Wednesday, March 15, 2017 7:54 PMHi JustMarie,
Thanks for the suggestion. I have a proc that has the sql which fetches data from different tables and inserts into destination table. I was not able to redirect rows from SQL task. So, I redesigned my package to use dataflow task. I am able to redirect error rows to a different table. I am getting error number and error column but not the error description. I am not sure how to get the error description. My package is still work in progress. Please let me if you happen to know to get the error description with the error number.Thanks for all your help!
Are you using 2016? If so, it's not difficult. If not, it is difficult!
It's not difficult if you use an OnError Event Handler. You can get the Error Description system variable and toss it into the field.
You'll also have to set the system Propagate variable since you don't want it to fail on the first error.
That should be enough to get you started on what you need.
March 16, 2017 at 9:55 am
JustMarie - Thursday, March 16, 2017 9:04 AMPhil Parkin - Thursday, March 16, 2017 6:33 AMSaig1417 - Wednesday, March 15, 2017 7:54 PMHi JustMarie,
Thanks for the suggestion. I have a proc that has the sql which fetches data from different tables and inserts into destination table. I was not able to redirect rows from SQL task. So, I redesigned my package to use dataflow task. I am able to redirect error rows to a different table. I am getting error number and error column but not the error description. I am not sure how to get the error description. My package is still work in progress. Please let me if you happen to know to get the error description with the error number.Thanks for all your help!
Are you using 2016? If so, it's not difficult. If not, it is difficult!
It's not difficult if you use an OnError Event Handler. You can get the Error Description system variable and toss it into the field.
You'll also have to set the system Propagate variable since you don't want it to fail on the first error.
That should be enough to get you started on what you need.
So rather than redirecting the row, an error is generated – which you handle. But what happens to the data in the row which generated the error, is it possible to send it to a bad-data table?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 16, 2017 at 1:50 pm
Phil Parkin - Thursday, March 16, 2017 9:55 AMJustMarie - Thursday, March 16, 2017 9:04 AMPhil Parkin - Thursday, March 16, 2017 6:33 AMSaig1417 - Wednesday, March 15, 2017 7:54 PMHi JustMarie,
Thanks for the suggestion. I have a proc that has the sql which fetches data from different tables and inserts into destination table. I was not able to redirect rows from SQL task. So, I redesigned my package to use dataflow task. I am able to redirect error rows to a different table. I am getting error number and error column but not the error description. I am not sure how to get the error description. My package is still work in progress. Please let me if you happen to know to get the error description with the error number.Thanks for all your help!
Are you using 2016? If so, it's not difficult. If not, it is difficult!
It's not difficult if you use an OnError Event Handler. You can get the Error Description system variable and toss it into the field.
You'll also have to set the system Propagate variable since you don't want it to fail on the first error.
That should be enough to get you started on what you need.
So rather than redirecting the row, an error is generated – which you handle. But what happens to the data in the row which generated the error, is it possible to send it to a bad-data table?
Good question. I'll set up a test package and go through some of the options to try to redirect error rows that include the error message.
March 16, 2017 at 2:33 pm
Hi Phil Parkin and Just Marie,
I am using SQL Server 2012. I found this article which explains on getting the error description.
https://msdn.microsoft.com/en-us/library/ms345163(v=sql.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
I have tried it with a simple test package and was able to capture the error description. Hoping I could use it in my real package. I will update you soon.
Thanks for all your suggestions and help.
March 16, 2017 at 3:16 pm
Saig1417 - Thursday, March 16, 2017 2:33 PMHi Phil Parkin and Just Marie,I am using SQL Server 2012. I found this article which explains on getting the error description.
https://msdn.microsoft.com/en-us/library/ms345163(v=sql.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
I have tried it with a simple test package and was able to capture the error description. Hoping I could use it in my real package. I will update you soon.
Thanks for all your suggestions and help.
Ah yes, I remember now: it's the name of the column with the error which is difficult to get (how far can you get knowing the column has Id 445?), not the description. Apologies for the confusion.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply