SSIS - Using MERGE T-SQL In OLE DB Source's Command Text

Question

[SSIS 2008 R2]

My Control Flow looks like this:

1. Truncate staging table in a DB (e.g. DB_Staging) local to the SSIS server -->

2. Load the staging table via a Data Flow from an external SQL Server (e.g. DB_SOURCE) -->

3. Use a T-SQL MERGE query to UPDATE, INSERT, and DELETE between the staging table and an table in a different DB (e.g. DB_DESTINATION) local to the SSIS server

For the third step, I have a Data Flow Task connecting to DB_DESTINATION. The Data Flow for this task has an OLE DB source set to SQL Command containing the T-SQL MERGE statement (query snipped for brevity):

MERGE   dbo.destination AS dest
USING   (SELECT * FROM DB_STAGING.dbo.source) AS src
ON      ...
    WHEN    MATCHED
            AND ...
            THEN UPDATE SET
                ...
    WHEN    NOT MATCHED BY TARGET
            THEN INSERT 
                ...
    WHEN    NOT MATCHED BY SOURCE
            AND ... 
            THEN DELETE;

The problem is, I am getting a validation error on the T-SQL MERGE query in the OLE DB Source. I'm new to SSIS, so I am not sure what is wrong. The queries to load the staging table and to MERGE work, as I have tested them in Management Studio. Can anyone offer any guidance? The validation error message from BIDS is:

Validation error. DFT_MergeData OLEDB_DB_DESTINATION [1]: No column information was returned by the SQL command.

Answer 1

The OLE DB Source in a Data Flow requires a statement that produces output in columns (a SELECT statement). The MERGE statement is not a valid statement for this.

If you want to use the MERGE statement, you would use it in a Execute SQL Statement task in the control flow, which would replace the Data Flow task.