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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply