CODEWITHSUNDEEP
sql-servercsvssis
sakshi jain
sakshi jain

Reputation: 331

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

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?

Upvotes: 2

Views: 626

Answers (2)

Hadi
Hadi

Reputation: 37313

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

Upvotes: 0

LONG
LONG

Reputation: 4610

The Flat File Source does not support dynamic file format, you have to use multiple sources to load these files.

Upvotes: -1

Related Questions