Merge Statement Ssis

I tested this code on SSMS Merge dim_BTS AS Target using ( SELECT A.BTS, D.idVille FROM onAir A INNER JOIN dbo.DIM_AXE_GEO D ON A.Ville = D.Villle

Solution 1:

Your Source sub-query is returning duplicate rows with same BTS (column You use to join on target) which is not allowed for MERGE statement.

You can refine your query to filter only the latest row for each BTS using ROW_NUMBER() function in CTE

WITH CTE_Source AS 
(
    SELECT  A.BTS, D.idVille, ROW_NUMBER() OVER (PARTITIONBY A.BTS ORDERBY d.idVille DESC)  RN -- choose order of your preferenceFROM onAir A  
    INNERJOIN dbo.DIM_AXE_GEO D   
        ON A.Ville = D.Villle   
)
Merge dim_BTS AS Target using  
(
    SELECT*FROM CTE_Source WHERE RN=1
) AS Source ON Source.BTS = Target.BTS  
WHEN MATCHED THENUPDATESET Target.idVille = Source.idVille; 

Or if multiple row BTS needs to be inserted, you need to add more columns on ON clause when joining on target.

Solution 2:

Have a look at your SELECT statement. You may have to add DISTINCT, or another JOIN condition, or a WHERE clause to make sure rows are not duplicated.