working with system::variables

Question

working with system::variables

AIRWALKER-375999

Hall of Fame

Points: 3040
More actions
April 27, 2012 at 5:28 am

#392133

Hi, I have configured a send mail task to fire when a data flow task fails. I want to message source of the mail task to be error description of the data flow task,
so in my send mail task I have configured the message source to be an expression:
"Error " + @[System::ErrorDescription]
when I deliberatlly cause the data flow task to fail (by passing invalid date/times) I get an error saying: "Failed to lock variable" in the send mail task
on a side note is it possible to pass the entire execution results of a package as an expression of a string variable and then use that variable to write to a file or as the message source of an email
thanks

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Where did you put the Send Mail Task? In an event handler?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

AIRWALKER-375999 Hall of Fame Points: 3040 More actions · Answer 2

yes in onError event handler of the data flow task. Thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Just for testing purposes, what if you changed the Send Mail Task to an Execute SQL Task? (and do some insert statement into a table for example)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

AIRWALKER-375999 Hall of Fame Points: 3040 More actions · Answer 4

what i did was change the source from a variable to direct input and left the input blank and then built the expression. Now it emails the error message, but it emails 7 different emails (identical to the 7 error messages in the execution results).

Is it possible to further refine the @[System::ErrorDescription] so that I just get the basic error message, which is:

[BookingRef [276]] Error: An error occurred while processing file "k:\Protected\SSIS\test\Import\BOOKING.csv" on data row 2.

Instead of all the ThreadCancelld, PrimeOutputFailed etc etc etc error messages. If thats not possible can I map a package level user string variable to the @[System::ErrorDescription] and then perform some sub string formatting on the string variable. Can I pass an EventHandler system variable to a user package level variable?

Thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

You'll need to parse the string yourself. Have fun with that 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

AIRWALKER-375999 Hall of Fame Points: 3040 More actions · Answer 6

I'm happy to parse the string, but where and how map do I pass the system variable to the user variable.

I tried doing it in package explorer by adding @[System::ErrorDescription] to the expression of the user variable but that didnt work, I also tried in the expression of the send mail task for the message source.

It complained of coverting a bool to a string but the error message is a string according to http://msdn.microsoft.com/en-us/library/ms141788%28SQL.90%29.aspx

AIRWALKER-375999 Hall of Fame Points: 3040 More actions · Answer 7

there is scripting solution to seperate out all the error messages returned by the main error message, it's quite simple to modify the code to suit your needs

http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/