CODEWITHSUNDEEP
sqlazureazure-data-factory
d2907
d2907

Reputation: 902

Update after a Copy Data Activity in Azure Data Factory

I've got this doubt in Azure Data Factory. My pipeline has a copy data activity, and after loading the information in the table I need to update a field in that destination based on a parameter. It is a simple update, but given that we do not have a SQL task (present in SSIS) I do not what to use. Create a SP for this does not seem to be the most appropriate solution, besides, modify the database is complicated. I thought the option "Use Query" in the Lookup activity could be a solution, but this does not allow me to create a SQL query with a parameter, just like in a Source.

enter image description here

What could be a possible workaround?

Upvotes: 0

Views: 5172

Answers (1)

JeffRamos
JeffRamos

Reputation: 923

You are on the right track with the Lookup. That is definitely the way to go. The query field there will allow you to create dynamic SQL just like you did within the copy activity. You just need to reference the variable/parameter properly.

Also, with the Lookup, it will always expect something returned. You don't have to do anything with that returned value. Just ignore it, but the Lookup will not work without returning something. So, that query field would contain something like:

UPDATE dbo.MyTable SET IsComplete = 1 WHERE RunId = @{pipeline().parameters.runId};
SELECT 0 AS DummyValue; -- Necessary for Lookup to work

enter image description here

Upvotes: 3

Related Questions