Custom email notification using Send Mail Task in SSIS

Let’s say your manager wants you to send summary information of the package (not just a one line message) that you’re running or even if there’s an error occurred during the package run. What will you do? I’m sure BI Express is a good option for this task, also you can create your custom message string and send it through send mail task and execute sql task by passing variable value.

I want to see summary of the package run in email msg, please include following information:

1) Package Start Time

2) Package Completed with Success/Failure

3) Look up Match Records

4) Look up Not Match Records

5) Package End Time

You can use the execute sql task in your package and create custom string using the sql code. After this step store the result set value as a single row and use the variable (SendMail)

 

SELECT 'SSIS Started: ' + CAST(? AS VARCHAR(50))

 + char(10) + char(13)

 + 'Package Completed Succssfully'

 + char(10) + char(13)

 + 'Lookup Match Records: ' + CAST(? AS VARCHAR(10)) 

 + char(10) + char(13)

 + 'Lookup NotMatch Records: ' + CAST(? AS VARCHAR(10))

 + char(10) + char(13)

 + 'SSIS Completed: ' + CONVERT(varchar(20), getdate(), 120)

Make sure, you use the correct parameter mapping for the (?) you use in your sql code(Step by step).

Once you're successfully created execute sql task then right click and run it to make sure, you're not getting errors. At this stage, you already got your custom message string created and store in a variable(Send Mail) value.

Next step, Use Send Mail task and pass that variable value as a message source here:

 You've successfully created your custom message using execute sql task and send it through Send Mail task to the email address you want.

You can also create the same logic for the error handling (when you want to send an error message notification with detail error message using event handler)

Create an on error event handler and use the execute sql task and send mail task there.

Write a sql code and store the result set as a single row (Error) variable

SELECT 'Package Name: ' + ?

 + char(10) + char(13)

 + 'Error Code: ' + ?

 + char(10) + char(13)

 + 'Error: ' + ?

Make sure you use the correct parameter mapping in the execute sql task. (You can add more information here as well as in sql code if you want). This is just an example. After this step simple use the send mail task and pass the variable(Error) value as a message source.

Hope, this information will be helpful to you.

Thanks for vising my blog.