Add Email to SSIS for troubleshooting

When your SSIS package fails, wouldn’t it be nice to receive an email with as much details as possible. Here are steps to setup this hidden gem.

Step 1. Setup the Event Handlers

  1. Click on the Event Handlers tab.
  2. Under the Event handler, select OnError
  3. Under the Executable drop-down-list, select the top most from the tree
  4. Click on the link

Step 2. Add the Send Mail Task

  1. From the SSIS Toolbox, drag the Send Mail Task into the Even Handlers canvas

Step 3. Setup the Email connection

  1. From the Connection Manager, right click in the white section and click on New Connection

Select SMTP and click Add…

Fill in the Name and SMTP server and click OK.

Step 2. Open up the Send Mail Task by double clicking on it

  1. Click on the Mail tab
  2. SmtpConnection: Click on the drop-down-list and select the connection you previously created
  3. Fill in the From, To fields with your emails. You can also use Expression for this but in this example, we’re going to hard code the emails

Click on the Expressions tab

You’ll want to do this step for the Message Source and Subject

Copy and Paste these to the respective Expression. When you’re done, click OK.

Message Source

“Machine Name: ” + (DT_STR, 2000,1252)  @[System::MachineName] + “\r\n” +

“Package Name: ” + (DT_STR, 2000,1252)  @[System::PackageName] + “\r\n” +

“User Name: ” +  (DT_STR, 2000,1252)  @[System::UserName] + “\r\n” +

“Source Name: ” + (DT_STR, 2000,1252)  @[System::SourceName] + “\r\n” +

“Source Description: ” +  (DT_STR, 2000,1252) @[System::SourceDescription] + “\r\n” +

“Error Code: “+ (DT_STR, 2000,1252) @[System::ErrorCode]   + “\r\n” +

“Error Description: ” + (DT_STR, 2000,1252)  @[System::ErrorDescription] + “\r\n”

Subject

“** Error Msg from OnError Event Handler *** ” +  ” SSIS Package Name:  [” +  @[System::PackageName] +  “] ” + “; Machine Name: ” + @[System::MachineName]

Proof is in the pudding!

Here’s a look at how it looks like when an error occurs. You’ll get an email telling you which Package and Task failed. Keep in mind that you no longer need to go to SQL Job History, SSIS Catalog,  Event Viewer to view the errors. The errors will come to you via email! Sweet Jesus!

As always, I hope this helps.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s