Handling files with different structure For each L

Question

There are 3 files in a directory and two of them are with same structure and the 3rd one has 3 more extra column in the end of the file.

file 1: columna,....,columnz 
file 2: columna,....,columnz 
file 3: columna,....,columnz,Column1,Column2,column3

Is it possible to import all these files using a single connection with in a for each loop container? If the flat file connection connection is based on the file 3 .

Answer 1

i just had the same situation which i tried to make it in a similar way. Didn’t thought about adding expressions in Data Flow. So, i created 2 packages to deal with individual file structure managed by 1 master package.

Answer 2

The only way to do it is load each row in as one varchar column and split it afterward.

Answer 3

No this is not possible in SSIS. Using a Flat File Connection designed for file 3 will corrupt data loaded from files 1 & 2.

I would build 2 Flat File Connections for the different layouts, then within the For Each Loop Container I would build 2 Data Flow Tasks for the two layouts. I would disable the Data Flow Tasks using an Expression, so that only the appropriate Data Flow Task executes against each file.