Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple Rows, split order when items total 720g

Status
Not open for further replies.

ChrisMarin

Technical User
Oct 14, 2002
23
GB
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...
 
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

FAQ219-2884
FAQ181-2886
 
make right order on SQL side and after that just send result for file in SSIS
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top