Search code examples
sql-serverssisssis-2012ssis-2008

Handling files with different structure For each Loop SSIS

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 .

Solution

  • 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.