SQL Server Azure Data Factory: Return Identifier values from a Copy Data activity

tvz2xvvm 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(139)

I am updating an on-premises SQL Server database table with data from a csv file using a Copy Data activity. There is an int identity Id column on the sink table that gets generated when I do the Upsert. I would like to retrieve the Id value generated in that table to use later in the pipeline.
Is there a way to do this?
I can't use a data flow as I am using a self-hosted Integration Runtime.
Hi @Nick.McDermaid, I am loading about 7,000 rows from the file to the database. I want to store the identities in the database the file comes from.
Edit: I have 2 databases (source/target). I want to upsert (using MERGE SQL below, with the OUTPUT clause) into the target db from the source db and then return the Ids (via the OUTPUT resultset) to the source db. The problem I have is that the upsert (MERGE) SQL gets it's SELECT statement from the same target db that the target table is in (when using a Copy Data activity), but I need to get the SELECT from the source db. Is there a way to do this, maybe using the Script activity?
Edit 2: To clarify, the 2 databases are on different servers.
Edit 3 (MERGE Update):

MERGE Product AS target
USING (SELECT [epProductDescription]
      ,[epProductPrimaryReference]
         FROM [epProduct]
        WHERE [epEndpointId] = '438E5150-8B7C-493C-9E79-AF4E990DEA04') AS source
ON target.[Sku] = source.[epProductPrimaryReference]
WHEN MATCHED THEN
    UPDATE SET [Name] = source.[epProductDescription]
      ,[Sku] = source.[epProductPrimaryReference]
WHEN NOT MATCHED THEN
    INSERT ([Name]
      ,[Sku]
    VALUES (source.[epProductDescription]
      ,source.[epProductPrimaryReference]
OUTPUT $action, inserted.*, updated.*;

Edit 3 (sample data):
source sample:
target output

sql-server

来源：https://stackoverflow.com/questions/74661868/azure-data-factory-return-identifier-values-from-a-copy-data-activity

1条答案

按热度按时间

r7xajy2e1#

Is there a way to do this, maybe using the Script activity?
Yes, you can execute this script using Script activity in ADF
As your tables are on different SQL servers first you have to create Linked server with source database on target Database.

go to >> Server Objects >> Linked Server >> New Linked server and create linked server with source database on target Database as below.
While creating linked server make sure same user must exist on both databases.

then I wrote Merge Query using this linked sever source.

My Sample Query:

MERGE INTO PersonsTarget as trg
    USING (SELECT [LastName],[FirstName],[State]
    FROM [OP3].[sample1].[dbo].[Personssource]) AS src
    ON trg.[State] = src.[State]
    WHEN MATCHED THEN
    UPDATE SET [LastName] = src.[LastName]
          ,[FirstName] = src.[FirstName]
    WHEN NOT MATCHED THEN
    INSERT ([LastName],[FirstName],[State])
    VALUES (src.[FirstName],src.[LastName],src.[State])
    OUTPUT $action, inserted.*;

Then In Script activity I provided the script