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 2012 Forums
 SSIS and Import/Export (2012)
 Send Email Data through SSIS

Author  Topic 

tooba
Posting Yak Master

224 Posts

Posted - 2014-06-21 : 03:23:40
Hi All,

I would like to know, is it possible way to transfer SQL Result in to Email Body and send email through SSIS.

Here is same data look like.

I have SP, Its a simple SP generating some result Here is sample result.

ID,LName,Fname
1,Smith,Carson
2,Norman,Sli
3,James,cliff

I am using SSIS
First Step:- SQL Task.

Exec MY Store Procedure

I know I can Attach or give this result to Variable and use this variable in Send Email Task.
But My question is, its a not a single result, it could be 3 rows or 19 rows.
I know someone going to suggest, use SSMS for this, but I cant due to security.
Please let me know if my question is not clear.
Any help would be great Appreciated.

Thank You.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-06-21 : 05:37:35
Yes its possible
1. store the resultset in a object variable created in SSIS
2. use a for each loop with ado enumerator mapping to variable and add resultset rows to single large string variable
3. use string variable inside sent mail task

Another option
1. Use data flow task and load the query result to flat file
2. use flat file as an attachment inside the sent mail task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -