Sending Mail in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

In most business requirement, business user would like to be notified about an execution status of SSIS package, through number of ways, such as logging. Another preferred way is by sending email to related user. SSIS provides this through Send Mail task. Scenario example for using this task when you would like to be notified by email when backup database task is failed or sales target has not reached minimum sales amount.

This task requires SMTP task connection manager. So you need to specify SMTP based connection manager before using the task. On the connection manager, you specify smtp server name.

On this SSIS version task, it has no longer dependency with MAPI. On DTS, it has dependency with MAPI, means that you should install Outlook on machine where the package will run.

Let see some properties for the task here:
SmtpConnection property contains name of connection manager you specified earlier. Another interesting property is MessageSourceType, there are 3 options, Direct Input, File Connection, and Variable, so it gives you more flexibility depending on business requirement. MessageSource will depend selection you make in MessageSourceType. As email priority, this task provide email priority through Priority property. And you can attach file through Attachments property. You can specify multiple attachments separated with semicolons. Unfortunately here, there is no way to define attachment property through variables.