Split fixed width row into multiple rows in SSIS

Question

I Have a fixed width flat file and that needs to be loaded into multiple oracle tables(one row needs to be split into multiple rows)

The numbers which are on top of each column is their size, and my desired output should look like shown below.

Flatfile data(fixed width):
3   6    3   11          3   10        3   10        3
ID NAME  AGE CTY1       ST1 CTY2      ST2 CTY3      ST3
200JOHN  46  LOSANGELES CA  HOUSTON   TX  CHARLOTTE NC
201TIMBER54  PHOENIX    AZ                CHICAGO   IL
202DAVID 32  ATLANTA    GA  PORTLAND  AZ

The occurrence may vary.. it can grow upto 20-30

DESIRED OUTPUT:
TABLE1
ID NAME  AGE
200JOHN  46
201TIMBER54
202DAVID 32


TABLE2
ID  SEQ CTY        ST
200 1   LOSANGELES CA  
200 2   HOUSTON    TX  
200 3   CHARLOTTE  NC
201 1   PHOENIX    AZ      
201 2   CHICAGO    IL        
202 1   ATLANTA    GA  
202 2   PORTLAND   AZ

Can some one help me out?

Thanks!

Answer 1

I would listen to the advice given by @bilinkc first and attempt to solve this with an unpivot.

Click here for details on how to use the SSIS Unpivot Data Flow Transformation.

However, if that does not work out for some reason and you really want to solve this with SSIS, I am (kind of) happy to say it is technically feasible to solve the problem using SSIS and one data flow.

Below are an abbreviated list of steps:

1) Add a Data Flow Task to your package

2) Add a Flat File Source to your Data Flow Task

3) Configure the Flat file Source with a Connection Manager for your flat file

4) Add a Multicast Data Flow Transformation to your Data Flow Task

5) Connect your Flat File Source with the Multicast Data Flow Transformation

Now the "fun" part (copy and paste can save you time here)...

6) Add 30 Conditional Split Data Flow Transformations to your Data Flow Task

7) Connect the Multicast Data Flow Transformation to each Conditional Split Data Flow

8) Configure each Conditional Split N to pull the row subset where State N and City N has a value

Example: Conditional Split 1

Output Name: CTY1_ST1

Condition: [CTY1] != "" && [ST1] != ""

9) Add 30 Derived Column Data Flow Transformations to your data flow

10) Connect each one to your 30 Conditional Splits

11) Configure each with a Derived Column Name SEQ and a value 1 to 30

12) Add a Union All Data Flow Transformation and Union All 30 of the data pipes back together

Now the "easy" part...

13) Add your first Sort Transformation to your Data Flow Task

14) Connect a 31st Multicast pipe to your first Sort Transformation

15) Put a check mark next to and sort by ID (Hopefully ID:NAME and ID:AGE is 1:1)

16) Check Remove rows with duplicate sort values

17) Add your second Multicast Data Flow Transformation

18) Add a second Sort Transformation to your Data Flow Task

19) Connect your Union All to your second Sort Transformation and sort by ID

20) Add a Merge Join to your Data Flow Task

21) Connect your second Multicast Data Flow Transformation as the Left Input

22) Connect your second Sort Transformation to your Merge Join as your Right Input

23) Configure your Merge Join as Join Type = Inner Join and select columns ID, SEQ, CTY, ST

24) Add your first OLE DB Destination to your data flow and connect your Merge Join to it (the result is TABLE2)

25) Add a second OLE DB Destination to your data flow and connect your second Multicast Data Flow Transformation to it (the result is TABLE1)