SQL - Add Header Row to SSIS Generated CSV Files
After generating a flat file in SSIS I needed to update that file with the column headers as the first row. In order to do this I used a SQL stored procedure.
STEP 1: Run the code below to create the stored procedure.
USE MIGRATION_DB
IF OBJECT_ID('GenerateBCPforSSIS') IS NOT NULL
DROP PROC GenerateBCPforSSIS
GO
CREATE PROCEDURE GenerateBCPforSSIS
(
@db_name varchar(1000),
@table_name varchar(1000),
@file_name varchar(1000)
) AS
Declare @Headers varchar(MAX),@HeadersRaw varchar(MAX),@sql varchar(MAX), @header_file varchar(MAX), @filename_short varchar(MAX)
--Generate column names as a recordset
Select @Headers = IsNull(@Headers + ',', '') + '""' + Column_Name + '""'
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC
--Create a dummy file to have header data
select @header_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'
set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@header_file+'" -c -C RAW -t, -S localhost -T'
print @sql
set @sql = 'type "'+@file_name+'" >> "'+@header_file+'"'
print @sql
set @sql = 'del "'+@file_name+'"'
print @sql
set @filename_short = reverse(substring(reverse(@file_name),1,charindex('\',reverse(@file_name)) -1 ) )
set @sql = 'rename "'+@header_file+'" '+'"'+@filename_short+'"'
print @sql
GO
STEP 2: Run the following command which will produce sql commands for all the views and tables in your database. You can add where clauses to the statements to limit which views/tables commands are created for. Note: Make sure to update the MIGRATION_DB string to the name of your database
USE MIGRATION_DB
SELECT 'exec GenerateBCPforSSIS ''MIGRATION_DB'',''' + name + ''',''E:\SSIS Output\' + name + '.csv''' AS CommandName
FROM sys.views
UNION
SELECT 'exec GenerateBCPforSSIS ''MIGRATION_DB'',''' + name + ''',''E:\SSIS Output\' + name + '.csv''' AS CommandName
FROM sys.tables
STEP 3: Copy the output from the command in step 2 and paste it into a new SQL query window.
STEP 4: Copy the output from step 3 and past it into a batch file.
STEP 5: Run the batch file, it will open your SSIS csv file and insert a header row with the column names.
Notes:
- The CVS file output from SSIS must match the names of your Views/Tables
- If you change, add, or remove any of the columns in your table/view you will have to run steps 2-5 again.
Comments
Post a Comment