INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Multiple Rows, split order when items total 720g

Multiple Rows, split order when items total 720g

(OP)
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...

RE: Multiple Rows, split order when items total 720g

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Multiple Rows, split order when items total 720g

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close