Report

Question

Report

Nita Reddy

SSCarpal Tunnel

Points: 4177
More actions
March 5, 2020 at 9:16 pm

#3731582

I never created SSIS
from SQL query output I need to send email to end User either in Excel or Text format.
How can I do in SQL or it has to be done via SSIs?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

In T-SQL, use sp_Send_DBMail (link) to send e-mails.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BTylerWhite SSCommitted Points: 1569 More actions · Answer 2

If the Excel attachment requirement exists, that will make things a bit more complicated to perform this using T-SQL. I believe it also requires 'Ad Hoc Distributed Queries' enabled as a server configuration and the proper drivers installed to work with the corresponding OLE DB provider.

As Phil mentioned, you can use sp_send_dbmail to send emails if you've configured Database Mail, using either 'TEXT' or 'HTML' as the body format. Here is a similar question posted on DBA StackExchange.

This can also be accomplished using SSIS as you have mentioned. Using a Data Flow Task to execute your query as a source and send the results to your destination file. Once the destination file has been populated, in your Control Flow you can use a Send Mail Task to attach the previously written file.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

If the requirement is to be able to open the attachment in Excel - then a simple CSV output file should open up by default in Excel. That can be done using sp_send_dbmail:

Declare @query nvarchar(max) = ''
      , @recipients varchar(max) = 'toemail@someplace.com'      --semi-colon delimited list of email addresses
      , @cc_recipients varchar(max) = 'ccemail@someplace.com';  --semi-colon delimited list of email addresses

    Set @query = '
    Set Nocount On;

Declare @startDate date = getdate() - 1
      , @endDate date = getdate();

 Select t.Column1 As [Sep=,' + char(13) + char(10) + 'ColName]
      , t.Column2
      , t.Column3
      , t.Column4
   From dbo.SomeTable                   t
  Where t.DateColumn >= @startDate
    And t.DateColumn <  @endDate;'
  
Execute msdb.dbo.sp_send_dbmail
        @profile_name = 'Database Mail Profile'         -- enter your database mail profile here
      , @query = @query
      , @subject = 'Some Subject Here' 
      , @body = 'Simple body message here - or build a full HTML body if needed'
      , @recipients = @recipients
      , @copy_recipients = @cc_recipients
      , @execute_query_database = 'DatabaseName'
      , @attach_query_result_as_file = 1
      , @query_result_width = 8000
      , @query_attachment_filename = 'SomeFileNameHere.csv'
      , @query_result_header = 1
      , @query_result_separator = ','
      , @query_result_no_padding = 1;

This is a simplified template - you can expand on it as needed. The trick here is the first column in the output - you must include the separator information to inform Excel that the file is comma-separated and it can then be opened directly from the attachment in Outlook.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs