Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Merge Inputs Several Rows but no Output


Merge Inputs Several Rows but no Output

Author
Message
Jay Bowers
Jay Bowers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 224
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?
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4511
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).

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Jay Bowers
Jay Bowers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 224
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?
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4511
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.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Jay Bowers
Jay Bowers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 224
I have not tried Union All. Let me see what kind of results I get using that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search