Email Query Results Using SSIS
-
Using SSIS and the Send Mail Task, I would like send a text e-mail. The body of the e-mail needs to be the results of a SQL Query.
I created a variable for the query. In the Send Mail Task, I set the Message Type to Variable and the Message Source to the variable that should return the result set. However, the e-mail that is received has the SQL statement (SELECT column1, column, 2 FROM table GROUP BY something). The result set is about 5 varchar columns and usually about 15 rows.
What step(s) am I missing. I'm not interested in HTML e-mail or attachments; I simply want to e-mail the results of a query. Any help is GREATLY appreciated.
-
I've had great luck using the sp_send_dbmail inside of an execute t-sql task.
The setup for that is pretty easy:
EXEC msdb.dbo.sp_send_dbmail
@profile_Name = 'ProfileName',
@recipients = ' recip@yourcompany.com',
@body = 'Here's the query you're expecting:',
@subject = 'Report Updated', -- here you could include a rowcount or some such thing
@query = 'Select left(insrt_dt, 12) date_added, ocd, ref_num, left(unit_cd, 6) unit_code
FROM sales.dbo.VSD_Enumeration
Where datediff(dd, insrt_dt, getdate()) = 0' -- of course you could use a variable here
For simple queries, I prefer this method to the send mail task. But not all environments will allow it so ymmv.
Good luck!
-
Check this discussion.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Hi
Did you find out how to send query result in mail using SSIS?
If yes can you describe here?
i am working on this issues and not going through SSIS.
Thanks
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply