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

i have multiple flat files(.csv) source in folder.each file has varying number of columns may or may not intersect other files. however, columns in source file present in destination table contains super set of these columns.

my requirement loop through each of these files , dynamically map columns available in file 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 populate table columns data available , null what's not every record. how can achieve using ssis?

you can adding 1 flat file connection manager add 1 column data type dt_wstr , length of 4000 (assuming it's name column0)

• in dataflow task add script component after flat file source
• in mark column0 input column , add 6 output columns (id, name, age, address, country, email)
• in input0_processinputrow method split column , assign values output column. (you can write logic want) can read answers @ following question example: reading csv file missing columns