Sending several files to several individuals using SSIS Send Email Task

  • Article

Recently I got a case and the customer was trying to create an SSIS package to e-mail results of several files to several individuals and he wanted to know if there is any inbuilt task in SSIS out of the box that can do this. He was saving few files in a folder and wanted to send some of the files from that folder that start with a certain series of characters (wildcards) as attachments to the emails.  He was trying to do that using SQL Server and SSIS 2008 R2.

After some research I was certain that there is no inbuilt task in SSIS that can achieve this directly. Then I started to work on alternate solutions using the exiting tasks that we have in SSIS. We came up with a solution and I thought there are other users out there who may need to do something similar from an SSIS package. So in this blog I will list the detail steps (with a lot of screenshots) of the solution that we provided to that customer. In summary we suggested the following as an alternate to achieve the customer’s goal.

I) Using a ForEach Loop container, you can loop over all the files in a folder, and save each file into a variable “MyFIle” file.

II) Inside the loop use a Script Task to append each file string from current file to a global string variable “Myfiles” + concatenate the | character, into a delimited list of individual files.

III) Then outside the loop (after it) use a send mail task with an expression set on the FileAttachments property to point to @[User::MyFiles]

Later I made a sample SSIS package implementing the above steps and it worked like a charm.  In the package I used a ForEachLoop Container and a Script Task to select multiple attachments from a folder using the wild card and then used a Send Email Task to send the email to multiple recipients with the attachments. Below I am listing steps with screen shoots.

1. Open up a new Integration Services Project under Boniness Intelligence Projects in Visual Studio 2008 and name it SendEmailTest (or any name that you want J)

2. Drag and drop a Foreach Loop Container.

3. Double click Foreach Loop Container task, go to the Collection Tab and do the following

a. Select “Foreach File Enumerator” in the Enumerator field

b. Click the browse button and navigate to the folder location where you have the files that you want to attach

c. Type the wild card string value as per your requirement

Example: Att*.txt à this will loop through all the files that start with “Att” and has the extension “.txt”.

After doing the above steps the Foreach Loop Editor should look as below.

 clip_image005

4. In the Foreach Loop Editor window select Variable Mappings tab and then click the drop down under Variable column and select <New Variable>. It will give you the “Add variable” screen.

 clip_image008

5. In the “Add Variable” screen name the variable as “MyFile” and also select the other options as below.

 clip_image011

6. Now click OK and you should see the variable “MyFile” added in the Foreach Loop Container as below.

 clip_image014

7. Clcik OK to the Foreach Loop Editor windows and then drag and drop a Script Task and place it inside the Foreach Loop Container.

clip_image016

8. From the top menu bar select SSISà Variable

 clip_image019

9. In the Variables pan you should already see the variable we added earlier “MyFile”. Type the name of another new variable “MyFiles” and select the other options as below.

 

clip_image022

10. Double click the Script Task to get the Script Task Editor window and click the browse button next to “ReadOnlyVariables”.

 clip_image025

11. Once you click the browse button in the Script Task Editor you will get the following Select Variables window.

 clip_image028

12. Check the variable “MyFile” and then clcik OK

13. Repeat steps 10 and 11 and select the variable “MyFiles” for the field “ReadWriteVariables”. After the selections the Script Task Editor should look as below.

 clip_image032

14. Now form the Script Task Editor window click the “Edit Script” button and add the following code in the Main() function and the click OK to complete editing the Script Task.

Note: The commented lines are for testing or debugging. I left them here in case you start to see issues and may need to debug J

public void Main()

{

// TODO: Add your code here

//Dts.Log("entering SCRIPT TASK.. ", 999, null);

Dts.Variables["User::MyFiles"].Value = Dts.Variables["User::MyFiles"].Value.ToString() + "|" + Dts.Variables["User::MyFile"].Value.ToString();

//MessageBox.Show(Dts.Variables["User::MyFile"].Value.ToString());

//MessageBox.Show(Dts.Variables["User::MyFiles"].Value.ToString());

Dts.TaskResult = (int)ScriptResults.Success;

}

So the Foreach loop will loop through the files in the specified folder and the script task will concatenate the file names in the “MyFiles” variable. Now we need to add a Send Email task and use the MyFiles variable to attach all the files.

15. Before we can add a Send Email Task we need to add a SMTP connection manager. Right click under the Connection managers pan at the bottom and select “New Connection”

 

clip_image034

16. From the Add SSIS Connection Manager windows select SMTP and click “Add” button. You should get the following SMTP Connection Manager Editor window.

 clip_image039

17. Specify your SMTP server and make sure this is a valid SMTP server. You need to enable SMTP service in your machine. Click OK to complete the SMTP connection Manager configuration.

18. Drag and drop a Send Email Task outside Foreach Loop Container.

clip_image041

19. Double click the Send Email Task to get the Send Email Task Editor. Select the newly created SMTP connection Manager form the dropdown. Type the From email address and then type the email address of the recipients separated by semicolon in the To filed.

 clip_image044

20. Click the expression tab from the left and set the value of FileAttachments field under Expression as @[User::MyFiles]. This will attach all the files in the email that we looped though in the Foreach loop.

 clip_image047

21. Click OK to complete the setting of the Send Email Task and run the package.

Reference:

Configure SMTP E-mail (IIS 7)

https://technet.microsoft.com/en-us/library/cc772058(WS.10).aspx

Note:

The SMTP server is not installed by default. SMTP can be added through the Features Summary area of the Server Manager tool in Windows Server® 2008.

Send Mail Task

https://msdn.microsoft.com/en-us/library/ms142165.aspx

SMTP Connection Manager

https://msdn.microsoft.com/en-us/library/ms137684.aspx

 

Author: MFarooq [MSFT]