Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge Inputs Several Rows but no Output Expand / Collapse
Author
Message
Posted Monday, December 14, 2009 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 9, 2014 11:17 AM
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?

Post #834027
Posted Monday, December 14, 2009 12:21 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
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
Post #834079
Posted Monday, December 14, 2009 1:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 9, 2014 11:17 AM
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?
Post #834123
Posted Monday, December 14, 2009 4:57 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
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
Post #834237
Posted Tuesday, December 15, 2009 6:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 9, 2014 11:17 AM
Points: 49, Visits: 224
I have not tried Union All. Let me see what kind of results I get using that.
Post #834453
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse