3
Vote

Merge Destination Not Merging

description

I'm trying to use the Merge Destination. I've set up a test using the dbo.DimCustomer table in the AdventureWorksDW2008 database. I have another table (called dbo.NewCustomer) that contains 3 updated records from dbo.DimCustomer and 2 new records. In the data flow, I'm getting the records from dbo.NewCustomer and pushing them into a Merge Destination. I've mapped the columns and everything seems to be okay. When I run the data flow, I see 5 rows going into the Merge Destination and everything turns green. However, when I look at the dbo.DimCustomer table afterwards, nothing has changed.
 
I've used SQL Server Profiler and I can see the statements coming into the database to create the TVP type and to create the merge stored procedure (both of which look fine). However, I never see the stored procedure getting executed. I've reviewed the TVPMergeDestination.cs code and it seems like it should be calling the stored procedure (when ProcessInput() executes, it calls SendDataToDestination(), which in turn calls m_mergeCommand.ExecuteNonQuery() to run the stored procedure). However, I'm not sure that it is as nothing changes in the dbo.DimCustomer table.
 
Help?
 
Dave F.

comments

TerriChen wrote Jan 8, 2009 at 6:53 PM

Did you choose the join or update field? According to your description, the generated merge script should be something like the following. Does your script include INSERT... VALUES...?

ON
TARGET.id = SOURCE.id
WHEN MATCHED THEN UPDATE SET
...
WHEN NOT MATCHED BY TARGET THEN
INSERT(...)
VALUES(...)

davefackler wrote Jan 9, 2009 at 9:02 PM

I believe I've found the problem. In my test, the destination table (dbo.DimCustomer) has 29 columns. The Merge Destination creates the table-valued data type using the schema from the destination table. Thus, the data type has 29 columns. However, in the data flow, I was only sending in four columns to the Merge Destination. The Merge Destination created the merge command correctly and created the stored procedure correctly. However, when the code tries to call the stored procedure, ADO.NET returns an error stating that an attempt to pass a table-valued parameter with only 4 columns is invalid as the table-valued data type (which the stored procedure refers to) contains 29 columns.

So, the question now is: does the Merge Destination only support scenarios where you have the same set of columns coming through the data flow and into the destination as the actual destination table has?

Dave F.

kevinidzi wrote Apr 2, 2009 at 4:30 AM

I've gotten that error or something like it as well. For me it was because I had the columns grouped in a non-alphabetical ordering coming as inputs, and the merge component (after i clicked into the advanced properties to perform the actual column mappings which was not done automatically) found that the component creates its output columns from the target, which was in a different ordering (alphabetical), which I was not able to manually reorder.

When the Dataset is built internally, i believe it uses the input columns and data to setup the columns (in the order they are in). When the TVF is constructed, it uses the columns from the table, so when you use it, the dataset columns were not in the same order as the TVF function dynamically created and so using it fails.

sipgnew wrote Jul 26, 2010 at 2:27 PM

Is it possible for someone who worked on this merge component to pls reply to all these questions.
  1. Pls can you update the component to do SCHEMA
  2. Why is there no DATA SAVED in database even when there is NO ERRORS and ALL records flowed into component
thanks

Gad

Sign in to add a comment or to set email notifications