Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Multiple Dynamic Attachments with the Task Factory Advanced Email & SMS Task

  • 1 October 2013
  • Author: Michael McIntyre
  • Number of views: 7363
  • 0 Comments

Recently I had a client inquire on the ability to have multiple dynamic attachments in an expression when using the Task Factory Advanced Email & SMS Task used in SSDT.

The normal way to add attachments is through the file system, using the “Attachments”, then “Browse” buttons:

Attachments Option

…but that would be directly attaching the specific file(s). I want it dynamic. And I want more than one. And I want my student loans paid off. I want a lot of things, so let’s move one step at a time, starting with what I know about this component and expressions.

I know I can set the attachment with a variable, which is done through the component’s properties in an expression, so let me start by setting up my variables.

Variables

Now I’ll go to the Advanced Email & SMS Task component’s properties and set up an expression for the “attachments”. I’ll make sure I click this Advanced Email component, then look at the properties in the window in the lower right of Visual Studio.

In the properties, I can see “Expressions”. Clicking the ellipsis on this line will bring me to the Property Expressions Editor.

Task Properties

Select “Attachments” from the Property Drop-down menu, then click the ellipsis to open the Expression Editor.

Property Expressions Editor

In the Expression Editor, I can drag down both my file variables. I understand how 1 would work, but how would I get two to work? A comma? A semi-colon?

Expression Builder

This is where I have to give credit where due. I prodded the big brains of two colleagues, Mitchell & Tammye who had been asked this question before.

To separate the variable file names, I needed to use a “pipe” to separate the variables in the expression, using the following format:

@[User::strFile1] + "|" + @[User::strFile2]

Evaluating the expression appeared okay, but a test would confirm, but let me review a couple of things first.

When I return the component editor, I can see that the component show both of my files in the attachment:

Attached Files

And if I change my variable:

Variables - Values

The attachments change on their own:

Updated Attachments

…and then I change it back to File2.txt.

I finish my editing to the Advanced Email & SMS Task and execute my package. Moments later: the moment of truth:

Email Header

 

Success. 

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

Please login or register to post comments.