SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


split fixed width row into multiple rows in SSIS


split fixed width row into multiple rows in SSIS

Author
Message
mtkr
mtkr
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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
JeffRush
JeffRush
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 446
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.
mtkr
mtkr
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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
JeffRush
JeffRush
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 446
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search