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

Question

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

Are you upserting only one row? What are you doing with the identity? Putting it into another table? I reccomend loading a staging table from the csv then doing all of your processing in stored procedures rather than trying to implement in ADF. — Nick.Mc, Dec 03 '22 at 02:09
@Nick.McDermaid, Is there a way to do it if there is only one row? — Christopher Page, Dec 03 '22 at 13:54
You can probably do it for one row but that is a very slow process. To get just one identity from the last inserted row, use `SCOPE_IDENTITY()`. To get the identities of a batch of inserted rows use the `OUTPUT` clause. I don't recommend that you wire up a a row by row ETL process in ADF, just use the ELT approach - insert all the records and process using stored procedures. — Nick.Mc, Dec 04 '22 at 23:27
Some examples here. There's really no need to pull the identity back up into ADF and do processing https://stackoverflow.com/questions/42648/how-to-get-the-identity-of-an-inserted-row — Nick.Mc, Dec 04 '22 at 23:29
Hi @PratikLad, the new ids would be a must, the updated ones might be useful. Do you still need input and output? Or does the `MERGE` statement clarify things? The only solution I've been able to find is to write to a staging table in the target db and do the `MERGE` using that table and copy the results back to the source db. — Christopher Page, Dec 07 '22 at 15:17
Not sure what the best way is to format the input and output data in the Stack Overflow editor. — Christopher Page, Dec 07 '22 at 16:39

score 0 · Accepted Answer · answered Dec 09 '22 at 12:19

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.

enter image description here

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

enter image description here

Note: In linked service for on premises target table use same user which you used in linked service

Executed successfully and returning Ids:

enter image description here

Thanks @PratikLad. Instead of creating a Linked Server, I created a MERGE table in the target db, using a Copy Data activity to transfer the data from the table in the source db. Then I did the MERGE in another Copy Data, in the Query Edit box, using a dataset pointing at the target db and wrote the output back, with the source data and the Ids of the target table, to the source db. But in principal, the same as your solution. BTW, how do you go about getting the output of the Script, in your example, to use later in the pipeline? — Christopher Page, Dec 13 '22 at 16:48

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

1 Answers1