Processing SCDs in bulk

I read another post. I'm hoping I'm just doing something wrong, and that the SSIS team wouldn't have done this:

I read in a batch of records that should cause changes in an SCD in some cases. The table is empty originally. In the wizard, its handling changing attibutes ,fixed and historical.

3 records in ( all with the same business key) = 3 records out?

In this case, it should only produce 1 record.

Are you kidding me? There must be a way to set the batch size to 1, right?

Are they all going down the "new" output?

I see your point. Not all of them are new are they and I assume they all valid to be in the pipeline. It'd be nice if the LOOKUP cahce (for that is what it is under the covers) could be updated as a row comes in - a dynamic cache if you will. I haven't much (any in fact) experience with the SCD component.

It could be that this is a hole in the product. Can you post a repro?

-Jamie

|||

For better or worse, this is by design. The data in the pipeline does not update the lookup table when the lookup is performed. Our data pipeline is buffered not single row so the initial row doesn't make it to the destination before the next row is compared and the SCD doesn't have a dynamic cache. If your data is like this then you would need to aggregate it for your initial insert case and then run it through again for your update cases.

Thanks,

Matt

|||thanks for responding. I found a work around by having a for each loop container that holds the dataflow component, and then passing through the set that way in some fashion, its just more complicated.|||

HI, I am very interested in this. Can you tell me what have been your solution? Something like doing inserts first (1st pass) and thn processing updates in another pass.

I need to imlement something like this in my project and it is the only way I found I could do it. If your solution is better that what I just described, can you share it please?

Thank you very much,

Ccote

|||

I put the dataflow inside a for each loop container.

In my case, I can identify ordered subsets of the original set, which I use as my pass criteria

In my case, we're processing policy transactions

e.g.

Policy# Transaction#

ABC 1

ABC 2

XYZ 1

XYZ 2

I want to process all the 1s first, then all the 2s, etc. so that if any SCD in the transaction occurs, it will be reflected.

For each transaction # , I make a pass through the dataflow, so I send through sets, and force the SCD to work. The beginning on my dataflow has a stored procedure call. I pass in the parameter of the transaction # I want it to process.

My SCD still isn't working, but its not related to the above.

hope that helps. good luck.