Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

split fixed width row into multiple rows in SSIS Expand / Collapse
Author
Message
Posted Sunday, March 31, 2013 6:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 14, 2013 7:59 PM
Points: 11, Visits: 113
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
Post #1437342
Posted Sunday, March 31, 2013 6:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:03 AM
Points: 121, Visits: 442
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.
Post #1437343
Posted Sunday, March 31, 2013 6:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 14, 2013 7:59 PM
Points: 11, Visits: 113
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
Post #1437344
Posted Sunday, March 31, 2013 6:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:03 AM
Points: 121, Visits: 442
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.



Post #1437345
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse