[Execute SQL Task] Error in SSIS Merge statement


[Execute SQL Task] Error: Executing the query "EXEC PC_Procedure_name1
EXEC PC_Procedure_name2..." failed with the following error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Resolution:

One of the options is presence of duplicates, before running the merge statement, please try to check the duplicates by running below query by combining more than 1 columns.
Scripts for finding duplicates by combining n number of columns
SELECT
q.KEY_FIELD,
COUNT(*)
FROM
(
SELECT
--(column1 ,column2,coulmn3)
CAST(column1 AS VARCHAR)
+CASTcolumn2 AS VARCHAR)
+CAST(coulmn3 AS VARCHAR)
 KEY_FIELD,*
 FROM Sourcetablename
 --where CAST(column1 AS VARCHAR)+CAST+CASTcolumn2 AS VARCHAR)+CAST(coulmn3 AS -----VARCHAR)='12059754183842012-08-26 00:00:00'
 ) q
 GROUP BY q.key_field
 ORDER BY COUNT(*) desc

 --307130
 SELECT COUNT(1) FROM  sourcetablename
 --10510844
 --10510844
Then change the MERGE Statement by adding one more column in the ON Clause to do perform merge.

Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)