Multiple Rows, split order when items total 720g

[ChrisMarin]

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?

Thanks in advance...

 

[fredericofonseca]

wrong way - SSIS on its own isn't even the tool for this.

give you one example.

assume lines orders with same details and with following weights in order
700g
25g
5g
695g
15g
500g

if you split only by order they are supplied you would get
order 1 - 700g
order 2 - 30g (25 + 5)
order 3 - 710g (695 + 15)
order 4 - 500g

when doing a proper validation of weights you should get
order 1 - 720 (700 + 15 + 5)
order 2 - 720 (695 + 25)
order 3 - 500

or

order 1 - 700
order 2 - 695
order 3 - 545 (500 + 25 + 15 + 5)

several ways of doing this and plenty of formulas/software around to do it (search for minimize cost linear programming).

but take in consideration that all of them imply multiple passes over the data

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gk53]

make right order on SQL side and after that just send result for file in SSIS