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

  1. Wow, that is a very good article about SSIS Upsert and its related aspects.
    SSIS Upsert

    ReplyDelete
  2. "SQL Injection" is subset of the unsubstantiated/unsanitized client input weakness ("cradle floods" are an alternate subset), https://onohosting.com/

    ReplyDelete
  3. This is a job which is basic to the accomplishment of your undertaking so assuming there is somebody you in your association who meets your prerequisites secure them for your venture by distinguishing them as a basic asset in your task sanction.https://hostinglelo.in/

    ReplyDelete

Post a Comment

Popular posts from this blog

Add User As Local Administrator On Domain Controller

Calling Dataverse Web API in PowerShell using Client Credentials

Windows Server 2008R2 VMs Shut Down After 1 to 2 Hours