Send Mail Task – Dynamic Recipient

Problem: We need to send email to multiple recipients that are not static and will be changing.

Solution: Below is how our control flow will look

 

Step1. First we will create a table and populate the table with Emails

create table EmailRecipient

(

ID int identity(1,1)

,Emails varchar(100)

,ISSend bit

)

 

insert EmailRecipient

values ('nehaa@devtechie.com',1)

insert EmailRecipient

values ('anoop@devtechie.com',1)

insert EmailRecipient

values ('ankit@devtechie.com',0)

Step 2.

Variables:

We will create two variables as shown below

Step 3. We will start configuring components in the control flow starting with Execute SQL Task. Create an OLE DB connection manager to be included here.

Resultset: Full result set

 

Step 3. Configure Foreach Loop Container as shown below

Step 4: Configure Script Task

 

Below will be the one line code inside Script task

public void Main()

{

// TODO: Add your code here

 

Dts.Variables["ToLine"].Value = Dts.Variables["ToLine"].Value + "; " + Dts.Variables["TempString"].Value;

Dts.TaskResult = (int)ScriptResults.Success;

}

After including SMTP Connection Manager to the project, configure Send Mail Task

Since the recepients to whom email will be sent to are configured in Script Task, ToLine will be mapped to the variable

 

 

 

 

 

Tags: , , , , , , ,

SSIS

Comments (2) -

Neha India, on 1/30/2013 5:16:08 AM Said:

Neha

Hi Nehaa,

It was real good to see this post.
I need a help from you but that might be related to your older post like: www.devtechie.com/.../...dy-via-Database-Mail.aspx

I wish to create a chart & send it to with the database email body. Will you please guide me, how to get this.

Thanks,
Neha Mathur

Nehaa Vishwa United States, on 2/5/2013 9:35:48 PM Said:

Nehaa Vishwa

Hi Neha,
The chart that you are trying to send, is it dynamic or just an image? If it is just an image then you can follow this post as is with your variables.

If this is a dynamic chart that is tied to data, then it would be better to provide link to that chart/portal, as email cannot contain dynamic data.