Data Flow SSIS - Common destination table, different structure flat files

Question

I have multiple flat files(.csv) as my source in a folder.Each file has varying number of columns which may or may not intersect with other files. However, all columns in any source file are always present in my destination table that contains the super set of all these columns.

My requirement is to loop through each of these files and dynamically map columns that are available in that file to the destination table(header names of csv file match column names in table).

Structure of File 1:
id, name, age, email

Structure of File 2:
id, name, age, address, country

Structure of File 3:
id, name, age, address

Structure of Destination Table:
id, name, age, address, country, email

I want to populate the table for all columns with data for what is available and NULL for what's not for every record. How can I achieve this using SSIS?

Most painless method is to insert all of the records from the three files into temp/staging tables and execute a stored procedure to populate the table with your logic inside. You can do all your merging logic in SSIS itself, but that's much more of a hassle... — Siyual

Hadi Hadi · Accepted Answer · 2017-04-14T21:12:14

you can do this by adding one Flat File Connection Manager add only one column with Data type DT_WSTR and a length of 4000 (assuming it's name is Column0)

In the dataflow task add a Script Component after the Flat File Source
In mark Column0 as Input Column and Add 6 Output Columns (id, name, age, address, country, email)
In the Input0_ProcessInputRow method split this column and assign values to output column. (you can write the logic you want) you can read answers at the following question to get an example: Reading CSV file some missing columns

Data Flow SSIS - Common destination table, different structure flat files

2 Answers