Handling files with different structure For each Loop SSIS

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 .

Mike Honey · Accepted Answer · 2014-04-16 03:33:33Z

5

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.

answered Apr 16, 2014 at 3:33

Mike Honey

15.2k1 gold badge29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

singhswat Over a year ago

I didn't catch the last piece, can you please elaborate (I would disable the Data Flow Tasks using an Expression, so that only the appropriate Data Flow Task executes against each file)

Mike Honey Over a year ago

I would set the Disable property on the Data Flow Tasks using an Expression. This assumes you have some way of identifying file 1 vs 2 & 3. The Expression on the DFT for file 1 might read: SUBSTRING ( @[User::MyFileName] , 1 , 5 ) != "file 1:"

Nick.Mc · Accepted Answer · 2014-04-18 12:00:26Z

2

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

edited Apr 18, 2014 at 12:00

answered Apr 18, 2014 at 11:49

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Comments

Sam · Accepted Answer · 2019-03-22 02:58:14Z

0

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.

answered Mar 22, 2019 at 2:58

Sam

11 bronze badge

Collectives™ on Stack Overflow

Handling files with different structure For each Loop SSIS

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related