ETLUser ETLUser - 4 months ago 11
SQL Question

How to send a query result in CSV format?

I am working on ETL and I have this below sql code in my SQL Task in SSIS package. This is how i have coded. I am selecting a data from a table and result of that query as file. I want this attachment sent in CSV format. How do i do it?

EXEC sp_send_dbmail @profile_name='default',
@recipients='dev@null.com',
@subject=@SUB,
@body=@BODY,

@query= 'SELECT [MID],[HID],[MeC],[LC],[RowCDate]
FROM [JBC].[dbo].[Table1] WHERE RowCDate >= GETDATE()
',
@attach_query_result_as_file=1


Any help will be very appreciated. Thanks in advance.

Answer

Adding @query_result_separator should do the trick.

EXEC sp_send_dbmail @profile_name='default',
@recipients='dev@null.com',
@subject=@SUB,
@body=@BODY,

@query= 'SELECT [MID],[HID],[MeC],[LC],[RowCDate]
FROM [JBC].[dbo].[Table1] WHERE RowCDate >= GETDATE()
',
@attach_query_result_as_file=1,
@query_attachment_filename = 'Results.csv',
@query_result_separator = ','

Adding @query_result_no_padding = 1 might clean up the results a bit. All off the arguments can be found here

Comments