How do I incorporate MERGE into my SSIS Data Flow Task?

Question

I am trying to prevent duplicates when I'm inserting values from a staging table into the primary table. I only have two components: an OLE DB SOURCE which points to an OLE DB DESTINATION.

In the OLE DB SOURCE, I originally had this:

SELECT [QVW Names] as 'QVW Name', '1' as 'QVW Type'
FROM dbo.STG_QVD_Generator
WHERE [QVW Names] <> ''
UNION
SELECT [QVW Names] as 'QVW Name', '2' as 'QVW Type'
FROM dbo.STG_Data_Models
WHERE [QVW Names] <> ''

To prevent the duplicates, I researched and began testing out a new query by using SQL Server Management Studio, I was able to get this:

MERGE INTO QVW_Files AS tbl 
USING (SELECT [QVW Names] as 'QVW Name', '1' as 'QVW Type'
       FROM dbo.STG_QVD_Generator
       WHERE [QVW Names] <> ''
       UNION
       SELECT [QVW Names] as 'QVW Name', '2' as 'QVW Type'
       FROM dbo.STG_Data_Models
       WHERE [QVW Names] <> '') AS src 
ON (tbl.QVW_Name = src.[QVW Name] AND tbl.QVW_Type_ID = src.[QVW Type]) 
WHEN NOT MATCHED THEN  
    INSERT (QVW_Name, QVW_Type_ID) 
    VALUES (src.[QVW Name], src.[QVW Type]);

and it seems to prevent duplicates from being entered into the target tables from the staging tables. However, I entered this into my OLE DB SOURCE and I realized that it would return no columns because this is not really a SELECT statement. How can I incorporate it into my Data Flow Task?

Thanks.

You may want to use `Execute SQL Task` on control flow with your statement. — Aleksey Ratnikov, Jul 21 '16 at 20:52
Possible duplicate of [SSIS 2012 - Insert new rows, ignore existing rows](http://stackoverflow.com/questions/19011049/ssis-2012-insert-new-rows-ignore-existing-rows) — Tab Alleman, Jul 22 '16 at 13:48

score 1 · Answer 1 · answered Jul 21 '16 at 21:06

1

You can use the Merge or Merge Join Transformation in SSIS. Create a Data Flow Task, setup each of your source tables as OLE DB Source objects, and then use Merge or Merge Join, and then set your Destination table.

answered Jul 21 '16 at 21:06

Jason B.

315
1
12

score 0 · Answer 2 · answered Jul 23 '16 at 01:00

0

Why not just use a SQL task to do the merge. Add it after your data flow.

answered Jul 23 '16 at 01:00

thomas

2,592
1
30
44

How do I incorporate MERGE into my SSIS Data Flow Task?

2 Answers2