Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Send Mail Task Help in SSIS

Author  Topic 

tooba
Posting Yak Master

224 Posts

Posted - 2013-01-14 : 18:35:45
I have quick question.

I am using Send email task in SSIS.
I create a table On SSMS, Below is a Table Structure.

ID, SSISPackage Description, TO, FROM
1, SAMPLE SSIS PACKAGE, ABC@hotmail.com;deg@gmail.com, ibm@live.com
2, Monthly Report, IGH@hotmail.com,love@live.com

What I want to use To and From Informatioin from Table. If i want to add or delete someone from
From or To List i can update Table not all SSIS Package. Please guide me how i can or can i accomplish this one?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-15 : 00:15:14
you need a for each loop for this.

First create a variable of type object in SSIS for storing resultset and two string variables for storing FROM, TO values

Use a Execute SQL task with full resultset option to populate recordset with from and to addresses. the query would be like
SELECT TO,[FROM]
FROM TableName
WHERE SSISPackageDescription = 'SAMPLE SSIS PACKAGE'
(it can be made dynamic by putting ? place holder and mapping to @[System::PackageName] in Parameters tab


Then inside for each loop configure to make use of Recordset enumerator and map it to above created variable. Inside for each loop create map string variable to recieve TO, FROM value during each iteration.
Now add a send mail task inside it and use expression builder to map FROM and TO variable values to From and TO property of send mail task.

On executing it will populate recordset with records from table and then for each loop will extract each FROM,TO values from variable and set send mail task with their values to send mails

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -