Sending several files to several individuals using SSIS Send Email Task

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.

 

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.

 

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

 

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

 

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.

8. From the top menu bar select SSISà Variable

 

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.

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

 

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

 

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.

 

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”

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

 

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.

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.

 

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.

 

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]

Comments

• Anonymous
  June 08, 2013
  Good one !!!

• Anonymous
  August 06, 2013
  Thank you for this post. I needed the same thing and it worked perfectly!

• Anonymous
  December 09, 2013
  Working Article !! Best article.... Thanks for posting and sharing your ideas.

• Anonymous
  January 30, 2014
  and this will send the email like this: In folder I have 1.pdf, 2.pdf,3.pdf Emails: 1@domain.com; 2@domain.com; 3@domain.com 1.pdf will be sent to 1@domain.com 2.pdf will be sent to 2@domain.com 3.pdf will be sent to 3@domain.com or 1.pdf, 2.pdf  and 3.pdf  will be sent to 1@domain.com 1.pdf, 2.pdf  and 3.pdf  will be sent to 2@domain.com 1.pdf, 2.pdf  and 3.pdf  will be sent to 3@domain.com pls help me I have to do de first situation but I think that this topic describe de second situation. Do you know how can I implement the first?

• Anonymous
  August 21, 2014
  Can you make youtube video for this process please. I am very very new to SSIS and need to do similar task

• Anonymous
  September 19, 2014
  Thank you so much for this post.  I tried so many other alternatives (unsuccessfully) but this worked like a charm!

• Anonymous
  March 24, 2015
  Hi Please suggest me how to send email using ssis with more than 255 characters. Please suggest how can we send email without using SSIS send mail task with more than 255 characters. Thanks in Advance, Nanda Email :- nandan417@gmail.com

• Anonymous
  May 09, 2015
  Good

• Anonymous
  June 01, 2015
  If email contents are different(Email contain different files in attachment), then you need to compaose 3 separate emails. So you can have send email task within the for each loop.

• Anonymous
  June 28, 2015
  can anyone help me , as my files are not appending in MyFiles variable , only the last one file is sent via email , whats the issue , m not getting it ...!!

• Anonymous
  August 20, 2015
  @Anna: For second option you can do, just append domain1,2 and 3 with "|". e.g.: ToLine: 1@domain.com|2@domain.com|3@domain.com Above code will work. @Biya: use Concatenation "|" e.g.: Dts.Variables["User::MyFiles"].Value = Dts.Variables["User::MyFiles"].Value.ToString() + "|" + Dts.Variables["User::MyFile"].Value.ToString();

• Anonymous
  October 23, 2015
  Hi Thank you for this. Can you help me under the send mail task error- Either the file  doest not exist or you do not have permissions to acces the file.

• Anonymous
  March 09, 2016
  Hi Guys,Please help. I have multiple excel files that I need to email to project managers based on the project number and project manager's name. For example one manager can have multiple project files that need to be emailed to them as attachment in one email. (FIlename -E033152U00_Managername)I have already created a utility using SSIS and SQL mail utility however each file attachment is sent separately instead of one email. So if manager has 3 files 3 emails are sent instead of one email with 3 attachment.Thanks,Manik