none
Need to email results of sql query in email body from SSIS Package

    Question

  • I'm not sure the best way to do this task.  Should I pass the results of the sql query to a result set or a variable? I want the query output to become the body of an email I'm sending out.

    TIA,

    Barkingdog

    Thursday, January 14, 2010 8:53 PM

Answers

  • You should probably use a Data Flow Task to use a Source component to issue the query, then route the results to a Flat File.  Then you can attach that flat file to the email.

    If you really want it in the body of the email, and not an attachment, you'll have to shove the results into a variable, and use property expressions to use that variable as the body of the email.  However, since you're using Expressions to do that, you'll be limited to 4000 characters in the body.
    Todd McDermid's Blog
    Thursday, January 14, 2010 9:22 PM

All replies

  • You should probably use a Data Flow Task to use a Source component to issue the query, then route the results to a Flat File.  Then you can attach that flat file to the email.

    If you really want it in the body of the email, and not an attachment, you'll have to shove the results into a variable, and use property expressions to use that variable as the body of the email.  However, since you're using Expressions to do that, you'll be limited to 4000 characters in the body.
    Todd McDermid's Blog
    Thursday, January 14, 2010 9:22 PM
  • As Todd mentioned that a better way could be to send the file as an attachment to the mail.
    However I feel at times if the content is small and we can send the content in the body it is easier for the end user(lesss number of steps.).

    I would prefer querying the table and send out mail using SQL Server rather than SSIS in that case.
    Why I say this is because SSIS does not support HTML tags in the body and if you use DB Mail you can format ur mail as per ur liking.
    Here is a sample code:
    DECLARE @tableHTML  NVARCHAR(MAX) ;
    
    SET @tableHTML =
        N'<H1>SQL Server: SQLSrv1</H1>' +
        N'<table border="1"></th></tr>' +	
    	N'<TR><TD>Col1 Header</TD><TD>Col2 Header</TD><TD>Col3 Header</TD><TD>Col4 Header</TD></TR>' +
        CAST((SELECT 
    
       'tr/@bgcolor' = CASE WHEN 'S' = 'S' THEN 'Green' 
             WHEN 'Q' = 'W' THEN 'Yellow'
             ELSE 'Red' END,	
    	'font/@Color'='WHITE',
    	td = 'col1',     '',
         td = 'col2', '',  
         td = 'col3', '', 
         td = 'col4'
     FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table>'
    
    print @tableHTML
    
    
    EXEC msdb.dbo.sp_send_dbmail @recipients='sudeep.raj@domai.com',
        @subject = 'SQL Errors Report',
        @body = @tableHTML,
        @body_format = 'HTML',
    	@profile_name = 'SudeepTest';
    PS. You need to have a DB Mail profile created before you can do this.

    Hope this helps !!
    Sudeep   |    My Blog
    Friday, January 15, 2010 9:20 AM
  • Or you could use the Send HTML Email Task on CodePlex.  (Never miss a chance to plug these... :) )
    Todd McDermid's Blog
    Friday, January 15, 2010 4:56 PM
  • Todd,

    You wrote

    >>> probably use a Data Flow Task to use a Source component to issue the query

    What component inside a Data task can be used to issue a sql query? I'm only familiar with the Control Flow Execute sql task


    TIA,

    barkingdog

    Friday, January 15, 2010 5:02 PM
  • The OLE DB Source component, and the ADO NET Source (in 2008 only) can issue "static" SELECT commands, and with some work, can issue "dynamic" SELECT commands.  You can even call stored procs that return recordsets.
    Todd McDermid's Blog
    Friday, January 15, 2010 5:11 PM
  • Todd,

    >> The OLE DB Source component, and the ADO NET Source (in 2008 only) can issue "static" SELECT commands,

    I am trying to send the the query output to a flat file but don't see how to do it.  The sql input query (actually several queries in one) used in OLE DB returns various row counts, no columns in particular, so there is no output column to specify (as the Flat File Destination seems to insist upon).



    Barkingdog



    Friday, January 15, 2010 5:31 PM
  • Tood,

    Amazingly, I just ignored the columns wanted, and was able to generate the flat file!

    BUT my input query is not a single query. it is actually several successive queries and OLE DB seems to just execute the first query. Short of calling a stored proc is there any way to have OLE DB  execute all the queries in the "Sql Command text" field?


    Barkingdog
    Friday, January 15, 2010 5:36 PM
  • I'm not understanding why you'd want more than one query executed. 

    Are they different queries returning differently structured datasets?  If so, make multiple data flows, and do one data set per flow.
    Todd McDermid's Blog
    Friday, January 15, 2010 5:47 PM
  • Todd,

    Yes

    >>> are they different queries returning differently structured datasets? 


    the "query" is actually several queries in one, each query returning a count related to a different table.

    Barkingdog

    Friday, January 15, 2010 6:08 PM
  • I love plugging this article.  It is kind of an old article, and you do need to sign up at the SQL Server Central website, but there is a lot of very useful info there and this method is very customizable and gives you more options than the default send mail task.  Plain text may be okay for techies, but if the recipient of the email is going to be someone further up in the corporate ladder, they do prefer pretty formatting.  ;-)
    Friday, January 15, 2010 6:56 PM
  • With everyone's help I finally got the whole "shebang" working. Thanks to all.

    I just wish that SSIS was more clearly documented, intuitive, and there was a better learning path than "trial and error". To be frank, a developer approached me with the task at hand and we thought it would be a nice application of SSIS. Indeed it is, but after the fight (i.e. learning curve) getting it to work under SSIS I could have written the entire proces in VB.NET is far less time. Oh well..... at least SSIS is graphical!

    Barkingdog

    Friday, January 15, 2010 7:38 PM
  • There is definitely a learning curve to it, no doubt.  Did you end up solving your issue by using several Execute SQL Tasks to get each variable one by one, or did you combine the SQL statement such that it returned one row?
    Todd McDermid's Blog
    Friday, January 15, 2010 8:02 PM