How To - Tasks and Transformations : Send Mail

In my last post, I discussed the basics of the Execute SQL task, and how to build it in both BIDS and Mist. Today, I’m going to discuss a more straightforward task – Send Mail – and show how you might use it with Execute SQL to create a simple workflow.

Background:

It should come as no surprise that the Send Mail task is used to send messages. A common use case is notifying a user about the success or failure of tasks in your workflow.

The properties that are unique to the Send Mail task align with email fields:

1. To
2. BCc
3. Cc
4. From
5. Priority
6. Subject
7. Attachments

Using BIDS:

To add a Send Mail task to a package in BIDS, first open the Toolbox tool window and find the Send Mail task.

Drag and drop the task onto your package’s design surface. You’ll notice that the task has a red X icon, indicating that there are errors.

If you open the error list, you’ll see some errors and warnings pertaining to the task.

You can begin tackling these errors by double clicking on the Send Mail task in the designer. This opens up a modal dialog that lets you set various properties on the task. The above errors all pertain to missing or invalid properties on the Send Mail task. We’ll need to edit the Send Mail task’s properties to correct the issues.

The From, To, Cc, BCc, and Subject properties are all strings so you can just enter text directly. BIDS will check that address lines have properly formatted strings.

For example:

The actual email message’s content can come from different sources. The MessageSourceType property indicates the type of source; its dropdown allows you to choose from:

Type Description Direct Input Enter text directly into a property File Connection Specifies a file that contains the content. The file may already exist or may be created at runtime. This may be used if you want the mail to include the contents of a log file. Variable Create or choose a variable that contains the content.

For the Direct Input scenario, you can enter text directly in the MessageSource field. While doing this, try changing the message’s priority to High so it gets Bob’s attention.

With the basic email ready, you now need to provide a SMTP Connection so that when the package runs, it can actually send this email. In BIDS, clicking on the dropdown button in the SmtpConnection field allows you to create a new connection by opening the STMP Connection Manager Editor.

The important field here is the SMTP server field. For this example, I entered a phony SMTP server; naturally, you’d enter a real one.

Finally, you can add an attachment. If you click inside the Attachments field, you’ll see a button with an ellipsis.

Click on the button to display an Open file dialog box, which allows you to select attachments for your mail. If you want to attach multiple files, you’ll need to open each one separately via the Open file dialog box. BIDS will add a | character between each file path. Note that this method of referencing files differs from elsewhere in SSIS, where connection managers are used instead.

With that, you can press OK in the Send Mail Task Editor dialog to return to the designer. You’ll see that the red X icon is gone since the task now has all the information it needs.

Now that you’re done with setup, you still need to know when to run the task. As mentioned at the beginning of this post, a typical use of Send Mail is to notify a user when a task succeeds or fails. For instance, if you had an Execute SQL Task that failed, you might want to tell someone.

For this example, imagine that you have the Execute SQL task that was built in the previous blog post. You can connect that task to the Send Mail task, so that the result of the Execute SQL task controls whether the Send Mail task runs. To do that:

1. Select the Execute SQL Task
2. Drag its arrow from the Execute SQL Task to the Send Mail task
3. Double click on the arrow

When you double click on the arrow, the Precedence Constraint editor will open. This dialog lets you control when the Send Mail task runs. In this case, if the Execute SQL Task fails (notice that the Value field is set to Failure), the workflow will follow your arrow by executing the Send Mail task.

Using Mist:

Now, I’m going to show you how to create the same Send Mail task and workflow in Mist.

To start, you’ll need to create a package. If you’re unfamiliar with how to create a package, you can follow the first couple steps in the Mist User Guide’s Creating a Package topic.

Double click on your package to open it in the designer. The designer will be empty since nothing has been added to the package yet.

Now is a good time to locate the Package Details and Toolbox tool windows. The Package Details window is typically located in the lower portion of the Mist window.

The Toolbox tool window is typically in the lower right portion of the Mist window.

Once you locate the Toolbox tool window, select and drag the Send Mail task onto the Package’s design surface.

Just as in BIDS, there’s a red X icon to indicate that the Send Mail task has errors. And just like in BIDS, the error list window lists the errors pertaining to this task

One thing you may have noticed when you dropped the Send Mail task onto the package is that the Package Details area is immediately updated to display the Send Mail task’s properties.

Just like in the BIDS dialog box, you can fill in addresses for From, To, Bcc, and Cc., along with a subject line.

If you click on the dropdown for the SMTP Connection,you’ll see that it only contains a (No Selection) entry, meaning that you need to add a new SMTP connection to your project.

To do that, you can follow the steps in the Mist User Guide’s Create New Connection section to create a connection, although you’ll want to select a SMTP connection type.

Once created, select the new SMTP Connection in the Logical View and switch to the Properties tab to see its values.

You’ll see the Smtp Server property has a red border, indicating that leaving this property blank produces an error. To fix the issue, you can enter smtp.example.com. This causes the red border to disappear, indicating that the error has been fixed.

Mist is all about reusing assets whenever possible. This SMTP connection can be used for every task in your project. Additionally, you can edit the connection in one place if you need to change it in the future.

To simulate the BIDS workflow, drag and drop an Execute SQL task from the Toolbox onto the design surface. Then drag a line from the bottom anchor of the Execute SQL task to the top anchor of the Send Mail task.

Once done, you’ll end up with an edge between the tasks. If you select the edge, the Package Details area updates to display precedence constraints in the workflow.

Notice that there’s a Data Grid that specifies the actual task whose output will control the execution of the Send Mail task. This is especially useful when a task has several inputs. As before in BIDS, you can choose an appropriate Evaluation Operation and an Evaluation value.

Alternatives:

For reporting errors on a whole container or package, you would typically take a different approach than the one we’ve outlined. You might be tempted to attach an OnError event handler to the container and, within the handler, add your Send Mail task. However, this approach is not recommended as it can result in being bombarded by error emails. Check out this link for a suitable way to handle this scenario.

Summary:

This concludes my look at the Send Mail task, and building a simple workflow with the Execute SQL task. Next time, I’m going to review another workflow task.

Links:

Send Mail documentation for the BIML language

Send Mail task - MSDN

Craig