Error Traping

Question

Error Traping

VRT

SSCertifiable

Points: 5776
More actions
November 12, 2008 at 12:14 am

#128691

Hi,
I created one SSIS package. It has 2 Data flow task. I am able to send mails if the error comes in Data flow task. For this mailing I am using a stored procedure.
The mail contains the body as the package is failed.
Now what I need is in the body I want keep error details and the record where the error is coming and which task the package failed.
Is it possible in SSIS? I know in DTS package we have error handling where we can track the error and the record that causes the error.
But I didn't get any clues in SSIS?
Please let me know if you have any kind of stuff to make this done?
Thanks in advance,
Venki.
Thank You

Viewing 9 posts - 1 through 8 (of 8 total)

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

pritesh2205 Ten Centuries Points: 1355 More actions · Answer 1

You can do this using OnError Event handler, see the tabs on the development studio, it will be next to the data flow tab.

In the event handlers tab , select the control flow component which you want to check for error in the executables drop down.

For this executable select onError even in the even drop down.

Now put the send mail task in the designer area and go to the properties of the the send mail task. Selct the expression to open the expressions editor, on the editor select the property dropdown and navigate to messagesource property. For this property set the expression in the editor.

In the editor you will have variables folder. On expanding it you will be able to see all the system and user variables.

Among the system variables you will see the system:errorcode and system::errordescription variables. Use these variables to build a message for your send mail task.

Note: These variables are only accessed in the OnError eventhandler Context.

Hope this helps

VRT SSCertifiable Points: 5776 More actions · Answer 2

Thanks Pritesh.

But the problem is that only. I can not use the Send Mail Task.

The DBA has prepared on SP to do the mailing. I have to give the parameter as FROM, TO, SUBJECT AND MESSAGE.

Now what I want is I want to prepare the message with the error number and error description and the row data that generating the error.

Can any one tell me Is it possible or not? Or else I will assign it to my DBA?

Thank You

VRT SSCertifiable Points: 5776 More actions · Answer 3

No one have the ideas to do this task?

Please share if you have any type of Idea?

Thank You

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 4

Are your recipients internal customers/users that would have access to a network share? Could you send a link to the appropriate flat file as part of the body of your email?

If so, you could redirect the error records, append the error details and send to a flat file destination. Then when you prepare the body of your email, you could include a link to the dir & file...

VRT SSCertifiable Points: 5776 More actions · Answer 5

dhandasyde (11/19/2008)
Are your recipients internal customers/users that would have access to a network share? Could you send a link to the appropriate flat file as part of the body of your email?

If so, you could redirect the error records, append the error details and send to a flat file destination. Then when you prepare the body of your email, you could include a link to the dir & file...

This idea is good, But How can I redirect the oledb source task if error comes?

how can I redirect from oledb destination task if error happens? This is not possible because I tried this but throwing error that can not redirect from destination task.

Thank You

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 6

I'm sorry if I'm oversimplifying things here...

If you select EDIT on the OLE DB Destination task, you can redirect the records that fail on the insert by going into the Error Output screen and configuring an error redirect. By default, the Error action is set to Fail Component but if you change that to Redirect Row, you can add a Flat File destination to write the error records and the error details to file.

Now at the Control Flow level, you can add an Execute SQL Task to send the email with a link to the flat file.

VRT SSCertifiable Points: 5776 More actions · Answer 7

dhandasyde (11/20/2008)
I'm sorry if I'm oversimplifying things here...
If you select EDIT on the OLE DB Destination task, you can redirect the records that fail on the insert by going into the Error Output screen and configuring an error redirect. By default, the Error action is set to Fail Component but if you change that to Redirect Row, you can add a Flat File destination to write the error records and the error details to file.
Now at the Control Flow level, you can add an Execute SQL Task to send the email with a link to the flat file.

Oledb destination task giving the following error when I configure as above___

The error row Disposition on INPut OLE Destination Input(4627) can not be set to redirect

Can you help?

Thank You

VRT SSCertifiable Points: 5776 More actions · Answer 8

venki (12/5/2008)
Oledb destination task giving the following error when I configure as above___
The error row Disposition on INPut OLE Destination Input(4627) can not be set to redirect
Can you help?

The problem is fixed and now I am able to redirect the rows now.

Thanks for your help?

I need to test this once.

Thank You