December 14, 2009 at 11:26 am
I've got a Data Flow that I am Conditionally splitting twice. DataSource is SQL2k DB, and Destination is SQL2k8 DB.
First Split is to see if a record exists in the Destination: No goes to Merge 1 and Yes goes to Second Split.
Second Split is to see if the record matches (description field): Yes goes nowhere, and No goes to Merge 1.
Merge 1 feeds an OLE DB command that is supposed to insert the reocrd into the table (that I've been comparing the Data Flow to in the prior Splits).
The desired behavior is that the Merge would feed results to the OLE DB command and insert the record. What is happening is that the Data Flow is being partially processed and the OLE DB command goes 'Green' before all the data is 'Merged'.
There are about 16k records in the incoing Data Flow.
Switching to OLE Destination doesn't change anything, because the output from the Merge never outputs any of the records, even though there are 81 records shown in Split 1's Lookup No Match Output.
Any ideas? Am I using this control incorrectly? Is there a better way to do this?
December 14, 2009 at 12:21 pm
If you are just trying to insert a row if it does not exist, why not use a lookup transformation? If your row has a unique key available, this should be a simple, easy way to do this. Set the error output for the lookup transformation to route failed lookups to an OLE DB Destination component (which would be better than OLE DB Command because OLE DB Command processes each row in the pipeline one-at-a-time).
December 14, 2009 at 1:26 pm
Sorry, lack of coffee this am caused the word 'Split' to be used in place of the word 'Lookup'. Yes, I am using look-up matching to separate the data flow into three sets. I was hoping to merge the two conditions that would warrant insertion of a new row into a single data flow before using OLE DB (command or destination) to insert the record.
I am surmising that this has something to do with caching, although that idea only comes from watching the order in which the steps ghange from yellow to green. Why is the OLE DB (command or destination) turning green before the (prior step) merge has finished merging?
December 14, 2009 at 4:57 pm
Why is the OLE DB (command or destination) turning green before the (prior step) merge has finished merging?
Good question. I would not expect this to happen. Are you trying to process a SCD with your Description field being a historical attribute? If so, have you tried using the SCD transformation? It's pretty straight forward and it will do the exists/changed comparison for you. That or you could try the UNION ALL transformation instead of the Merge.
December 15, 2009 at 6:51 am
I have not tried Union All. Let me see what kind of results I get using that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply