Import CSV. Multiple Rows. Split order when items total 720g

Question

Import CSV. Multiple Rows. Split order when items total 720g

chris-1111548

Valued Member

Points: 63
More actions
September 23, 2015 at 4:03 pm

#308024

SSIS 2008
Hi there,
we import orders for our clients from .csv text files that they provide us each day.
For us to achieve the maximum discount from Royal Mail our items can be no more than 720g in weight.
The .csv files are supplied with a row per item ordered, with all the generic order details (name, address, contact details, etc.) repeated on each row.
eg.
Name, Address1, Town, PostCode, Quantity, Item, Weight
Mr John Smith, 1 Smith Road, London, NC1 2BB, 1, Colouring Book, 200
Mr John Smith, 1 Smith Road, London, NC1 2BB, 1, Maze Book, 234
Mr John Smith, 1 Smith Road, London, NC1 2BB, 1, Log Book, 544
Mrs Joan Jones, 4 High Street, Birmingham, B22 4FR, 2, Piggy Diary, 133
Mrs Joan Jones, 4 High Street, Birmingham, B22 4FR, 1, Piggy Coaster, 223
Mrs Joan Jones, 4 High Street, Birmingham, B22 4FR, 2, Piggy Badge, 33
Mrs Joan Jones, 4 High Street, Birmingham, B22 4FR, 1, Piggy Annual, 390
We currently sort the data by Name, Address1 and PostCode, then run a VBScript that adds incremented LineNumbers based on the previous row having the same Name, Address1 and PostCode data. If different, then the LineNumber reverts back to 1 and starts again.
How can I use SSIS to split the order in a similar way when the total weight would exceed 720g?
Or am I looking about this the wrong way?

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

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

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

chris-1111548 (9/23/2015)
SSIS 2008
Hi there,
we import orders for our clients from .csv text files that they provide us each day.
For us to achieve the maximum discount from Royal Mail our items can be no more than 720g in weight.
The .csv files are supplied with a row per item ordered, with all the generic order details (name, address, contact details, etc.) repeated on each row.
eg.
Name, Address1, Town, PostCode, Quantity, Item, Weight
Mr John Smith, 1 Smith Road, London, NC1 2BB, 1, Colouring Book, 200
Mr John Smith, 1 Smith Road, London, NC1 2BB, 1, Maze Book, 234
Mr John Smith, 1 Smith Road, London, NC1 2BB, 1, Log Book, 544
Mrs Joan Jones, 4 High Street, Birmingham, B22 4FR, 2, Piggy Diary, 133
Mrs Joan Jones, 4 High Street, Birmingham, B22 4FR, 1, Piggy Coaster, 223
Mrs Joan Jones, 4 High Street, Birmingham, B22 4FR, 2, Piggy Badge, 33
Mrs Joan Jones, 4 High Street, Birmingham, B22 4FR, 1, Piggy Annual, 390
We currently sort the data by Name, Address1 and PostCode, then run a VBScript that adds incremented LineNumbers based on the previous row having the same Name, Address1 and PostCode data. If different, then the LineNumber reverts back to 1 and starts again.
How can I use SSIS to split the order in a similar way when the total weight would exceed 720g?
Or am I looking about this the wrong way?

The question is, what do you want to do at 1440g? Another split?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chris-1111548 Valued Member Points: 63 More actions · Answer 2

Yes, I would imagine the count resets each time. So the first Weight Total would be 550g (as the next row would push it over 720g); the counter would then reset to 0 and start adding the weights together again.

I wouldn't expect it to calculate the best combination of weights to make 720g (I think that would be too hard to achieve), just add the rows in a kind of loop and when it would go over 720g then restart again.

I hope that makes sense?

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

Apologies. I lost track of this one mostly because it's for Integration Services, which I don't use. Did you ever get an answer to this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)