Need to construct an error message in SSIS

Question

Post reply

Need to construct an error message in SSIS

Judy Scheinuk

Ten Centuries

Points: 1307
More actions
October 25, 2010 at 6:53 pm

#242237

This is my first SSIS package. It runs a stored procedure on each of 12 servers that deletes users. The stored procedure sends back errors in an output parameter. I am able to pick up this value. If there are errors, the package does not continue to the next step. If there are no errors, the package continues and copies a log of what was done from each server to a master server. I would like to send the results of each step in a single e-mail. I was thinking it could be done something like building a dynamic SQL string - @CMD = @CMD + @RESULTS but I can't figure out how to do this. I only want to send one e-mail at the end. Any ideas about how I could do this?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

Judy,

Create event handlers for package completition that are fired only when the package completes it's execution cycle.

Though, I am not clear with exactly what is the requirement...please elaborate

Raunak J

Judy Scheinuk Ten Centuries Points: 1307 More actions · Answer 2

There are 12 servers. For simplicity, let's say I run step 1 and step 2 for each server. If step 1 fails, I pick up the error from the server and the package does not go to step 2. When step 2 completes, I just need a message like "step 2 completed on server XXX with 6 rows written to the log." So I will end up with 12 messages of success or failure. I do not want to send the user 12 separate emails. I would like to build an email message somewhere and send that at the end. I was thinking about building it by concatenating the messages although I don't know if I can get a decently formatted message that way. This morning I woke up with the thought to write each message to a table somewhere, preferably not a real table. I would use the contents of the table for the message text. I will also be sending an attachment. I am open to suggestions! Thank you -

Judy

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

I often use On Error Event Handlers to append error messages to my own user defined ErrorMessages variable. At the end of the execution, if the length of that variable is greater than 0, I'll send an email with the content of that variable being part of the email message.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 4

Judy Scheinuk (10/26/2010)
There are 12 servers. For simplicity, let's say I run step 1 and step 2 for each server. If step 1 fails, I pick up the error from the server and the package does not go to step 2. When step 2 completes, I just need a message like "step 2 completed on server XXX with 6 rows written to the log." So I will end up with 12 messages of success or failure. I do not want to send the user 12 separate emails. I would like to build an email message somewhere and send that at the end. I was thinking about building it by concatenating the messages although I don't know if I can get a decently formatted message that way. This morning I woke up with the thought to write each message to a table somewhere, preferably not a real table. I would use the contents of the table for the message text. I will also be sending an attachment. I am open to suggestions! Thank you -

Use a Seq Container within your package and as Alvin had suggested use custom variables to store the text message for {Success/Failure} and OnCompletition of Seq Container trigger the Email

Raunak J

Judy Scheinuk Ten Centuries Points: 1307 More actions · Answer 5

It is the appending part that I am having trouble with. I can send the email if my source is a variable and I simply send user::errorout. I have been trying to append the error variable to another variable called "Mailmsg" but I keep sending strings of unresolved SQL as my message. Could you tell me how to append my one variable to another?

Sorry - as I explained, this is my first SSIS project and I don't have it all figured out yet.

Judy

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

I don't know that this is the preferred way to do it, but when I need a summary email with multi-step information, I often first create a table of errors and have each step INSERT any errors to the table. This includes the runtime, the nature of the error, some table PK values for the rows that caused the error (to make later troubleshooting easier), etc.

Then, I create an SSIS sendmail task to select rows from the errors table matching today's date and email me the data. (Actually, I create an SSIS variable to hold CountRows for errors and only email if errors were found.)

The benefit to me is that I have a permanent record of errors encountered, so I can track numbers and types of errors detected over time. These errors are usually user data entry errors, so that helps me harass/train staff 🙂

In your case, you could log the rundate, the server name, which users were deleted, etc. In fact, it sounds like you're already doing that, so why not query the master server errors table you created and do the sendmail from that?

Rich

Judy Scheinuk Ten Centuries Points: 1307 More actions · Answer 7

Thank you, Rich. That's what I decided I needed to do. I was trying to avoid creating another table. I already log users deleted, by whom, date etc. but what I needed was a message out of SSIS for each server - that it either succeeded or failed. It would fail if there was nothing to process on those servers or if it encountered certain other fatal conditions. I have resorted to running sp_send_dbmail in an execute SQL task so I can send the entire contents of the table as query output. I hope to attach a copy of the other log to show details. It's pretty much working now.

I'd also like to thank the person who suggested using the sequence container task. It turned out to be necessary so I could set server specific parameters at the sequence container level.

I'm going to keep this question open for a few days until I'm satisfied that these suggestions will work 100%. It's almost there...

Rich Mechaber SSChampion Points: 11130 More actions · Answer 8

Great! And thanks for posting back -- always good to hear that we helped you out.

You'll be happy you have that table, some day. Maybe you'll use it to track numbers of deleted users by server. Maybe you'll want to confirm that your agent job removed a particular user from a server on a particular date.

Rich

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 9

Rich,

Thanks for the alternate. 🙂

Judy,

HTH

Raunak J

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

rmechaber (10/27/2010)
I don't know that this is the preferred way to do it, but when I need a summary email with multi-step information, I often first create a table of errors and have each step INSERT any errors to the table. This includes the runtime, the nature of the error, some table PK values for the rows that caused the error (to make later troubleshooting easier), etc.
Then, I create an SSIS sendmail task to select rows from the errors table matching today's date and email me the data. (Actually, I create an SSIS variable to hold CountRows for errors and only email if errors were found.)
The benefit to me is that I have a permanent record of errors encountered, so I can track numbers and types of errors detected over time. These errors are usually user data entry errors, so that helps me harass/train staff 🙂
In your case, you could log the rundate, the server name, which users were deleted, etc. In fact, it sounds like you're already doing that, so why not query the master server errors table you created and do the sendmail from that?
Rich

I don't like the table idea, by itself, for the email message because it probably won't work if you have errors cause by connectivity problems with the same server that conatians the table(s). Using the user variable idea, all the data is all contained within the package.

I do agree with, and highly recommend, logging to SQL tables as indicated by rmechaber, for everything other than the "status/error" emails.

The logic that appends the error information to the user variable could also write the same information to SQL tables.

Good idea rmechaber. We just differ in preferences.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]