Emailing a small SQL result set as text within body of message

• Richard-756144

  Grasshopper

  Points: 17

  More actions

  May 22, 2008 at 11:53 am

  #129188

  Hi,

  I have created an SSIS package with various tasks that update a status table in a SQL Server database. Typically the status table has about 14 rows and has 4 columns.

  I would like to get that result set as text appended to an email that the SSIS sends out. In SQL2K I used to be able to do something similar with xp_sendmail where you could run a query.

  I have defined an object type variable and think I have assigned is to the full result set of the simple SQL Select statement. But I could do with someone taking me through that just in case I haven't got that to work.

  But if the variable is being assigned all 14 rows, how do I then append it to some introduction type text that the email task currently uses. Actually, forget the append bit, how do I get the result set appearing (nicely) as the body of the email.

  Thanks, Richard

• pduplessis-723389

  SSChampion

  Points: 10599

  More actions

  May 23, 2008 at 4:25 am

  #819578

  Hey Richard,

  The send email task will do this for you. Try the following:

  (a) Export the results as text as a dataflow task prior to sending the mail

  (b) In the send email task, add the attachment.

  Now to get a little more funky, the connection manager that you specified in (a) could be re-used in (b).

  i. Add a variable containing the connection string to be used in (a). Add this as an expression in (a) under the connection string

  ii. Add the same variable containing the connection string to be used as the attachment in (b) as an expression under the file attachments

  Trust this helps

  ~PD