SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Send Mail Task Help in SSIS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

165 Posts

Posted - 01/14/2013 :  18:35:45  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/15/2013 :  00:15:14  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000