Export sql server query output from ssis to text file INCLUDING THE COLUMN HEADERS

Question

I have been trying to export the output of a SqlServer sql query (stored procedure) to a text file. I achieved it using a Data Flow task with an OLEDB source and a Flat File Destination.

I would also like to get the Column headers along with the query result in the text file , but I cannot figure out how I can include the column headers.

I would like to do this dynamically from within SSIS

Is there anyone who knows how to do this ?

Answer 1

1. OLEDB Source, coonect the database. Use a table or preferably a SELECT statement (not select *, as you are aware, I am sure).
2. Flat File Destination. Connect them together. In the Flat File Destination Editor, click New... Select Delimited (for example). Give a File name. Say C:\temp\a.csv. Check mark Column names in the first data row.Click on Columns section in the left pane. You should see your column names. You can change these names in the Advanced section. Now, go to the mapping coulmn, and map the columns.

Special Note: Do not use a select statement in a stored proc as your data source. Ref: http://www.jasonstrate.com/2011/01/31-days-of-ssis-no-more-procedures-2031/

Please respond back.