Send mail task in SSIS - SQL Server Forums

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 in SSIS

Author

Topic

vtechies
Starting Member

4 Posts

Posted - 2011-07-06 : 00:24:39

Hi All,

In SSIS,can i send the result of query through send mail task to respective users.

I have tried the below ones but failed.

1.Through Execute sql task ,i have connected to my DB and also builded my query ,where results are succesfully formed and linked the same with send mail Task.

When executing the Task ,it sends mail but nothing is displayed in Source message

My expectation is to deliver the results of query in the source of the message.

2.I have created a ssrs rdl for particular query and report is generated succesfully,can i link it with ssis mail task.

When trying only rdl attachment is possible.

3.I need to send different reports to 50 different users which cannot be possible through SSRS Subscription as i need to create 50 different reports,deploy the same,so iam depending on SSIS or is there any other means can i send the report.

Thanks for any help

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-06 : 03:04:30

Have you configured "database mail" properly? Have you sent any mails from the database successfully at all?

-> http://msdn.microsoft.com/en-us/library/ms175887.aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

vtechies
Starting Member

4 Posts

Posted - 2011-07-06 : 04:38:11

Yes,i can send mail succesfully through send mail task through SSIS.

My problem is i can't email with reports in it.

Example:

By running query ,following results in displayed in table.
ID Name Branch Nlead
1 A x 3

Now i need to send this table as source message to users with all contents above displayed.

I can send the current report through ssrs by deploying report and using subscription option,

And my problem is i had 50 different users to send this report with altered values as

user1:

ID Name Branch Nlead
1 B Y 23

user 2:

ID Name Branch Nlead
1 C Z 43

and so on .

I can't deploy 50 reports and provides subscription for them.

Hence i guess there is some way in SSIS .

Users need to view the reports in email of their relevant data .

can it be achieved through SSIS.

Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-06 : 05:06:52

The best way would probably be to create a data driven subscription in SSRS instead. Much easier.

-> http://msdn.microsoft.com/en-us/library/ms159150.aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-06 : 13:49:03

you need to set expression for message source property of send mail task for sending query result as a subject. But for getting query result you might need to use a execute sql task and get result in a ssis variable for mapping in dynamic property tab.Anyways for your above scenario i think best option is to use data driven subscription as Lumbago suggested.

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

vtechies
Starting Member

4 Posts

Posted - 2011-07-07 : 05:02:50

Hi,

Thanks Lumbago and Visakh .

DataSubscription option is not viewable in my reports.

can you provide more idea on sending query results on sql task by setting expression and get result in a ssis variable for mapping in dynamic property tab

Any steps how to do it.

lightsql
Starting Member

17 Posts

Posted - 2011-07-20 : 05:39:35

I think your package should look like this,

1. EXECUTE SQL TASK -- create an object variable that will hold your result set
2. FOR EACH LOOP CONTAINER -- that will run through the content of the variable (from above) and place them to another variable. Add script task inside.
2.1. SCRIPT TASK -- create a variable that will string together your query. The purpose of this is because you can only use a string variable for the message source.
3. SEND MAIL TASK -- The MessageSource will contain the string variable.

vtechies
Starting Member

4 Posts

Posted - 2011-07-26 : 07:45:47

Hi Lightsql ,can you please explain the same with example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources