split fixed width row into multiple rows in SSIS

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

    the numbers which are on top of each column is there 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

  • Once you have the flat file source set up correctly, you could multicast and then send to two OLEDB destinations mapping the fields you want for each.

  • Thanks for the response

    If its a straight move to two tables i can do wht u mentioned.

    But the data into table2 need to be split for various cities and states for each ID

  • Sorry, I read too fast and missed that part.

    For the stream that will go into table2, you can use unpivot, specifying ID as passthrough, Seq as PivotKey column, City1/ST1 with pivot key value 1; City2/St2 with pivot key value 2, etc. and destination columns of City/St.

    That would give you a dataset in the format you are wanting. Then you could just map that to your destination.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply