SSIS 2008 - Combining rows from the same table

Question

SSIS 2008 - Combining rows from the same table

awalker 76056

SSC Rookie

Points: 42
More actions
October 2, 2017 at 9:02 am

#402557

Hi,
I'm quite new to SSIS so I'm more asking for to be pointed in the right direction here than for an answer but my problem is as follows:
I have a table like:
[Line Type]        [Product/Location ID]        [Quantity]
Product,             1,                                      NULL
Location,            1,                                      2
Location,            2,                                      1
Product,             2,                                      NULL
Location,            1,                                      1
and so on. Any number of location rows can occur below a product row but all relate to the before product. I have put it into a record set in SSIS and I am trying to loop through all the rows. When it is a product row assigning the productID to a variable. When a Location importing the current ProductID along with the LocationID and Quantity into a table.
The problem occurs when I create a dataflow inside the "For each loop" as I am unsure what to put as the source. Without the source it appears to be ignoring the conditional split.
Any hints would be appreciated.
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

I'm not sure you need a For Each loop container, because the default assumption is that your recordset get's processed in a manner that treats each row to all the transformations that follow. However, I'm far more concerned with the fact that your data is not very well structured at all, and accomplishing your objectives may be more complicated than what an SSIS package can actually handle, as a For Each loop container isn't going to give you a traditional procedural language type of loop, where you can program in all the necessary movement back and forth between rows That's just not how that operates. Can you provide more details, including DDL (table create and insert statements with sample data), and the expected output based on the sample data? You really need some kind of "group identifier" that is part of your data for this to work. SQL Server is a set-based database engine, and rows are not necessarily delivered in their original order, so I suspect you have a non-SQL Server data source of some kind. Please elaborate.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Please confirm what your data source is and what your target is.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

henryKrinkle Mr or Mrs. 500 Points: 556 More actions · Answer 3

Hi,

If you already have the data in a table and can guarantee the order they hit ssis, you could use a script component to check the value of line type. If it's a 'product', then set a variable value to store until the next product comes along, if it's a 'location', then output the variable and the columns you require to your destination.

I think you could get away with a simple if..else statement in this case.

Good luck

HenryKrinkle

awalker 76056 SSC Rookie Points: 42 More actions · Answer 4

Hi Henry,

That has worked exactly as I would like. Thank you very much for the help.

Thanks,

Adam

henryKrinkle Mr or Mrs. 500 Points: 556 More actions · Answer 5

That's great news. Now stop eating crisps and finish the job 🙂