CODEWITHSUNDEEP
azure-data-factory
Paul Cavacas
Paul Cavacas

Reputation: 4454

How to drive a Azure Data Factory based off of SQL Server values

I have one Pipeline setup in Azure Data Factory that has a pipeline variable setup called MonthsBack, lets say 12 as a default. When in an Until loop I build a SQL statement that uses this MonthsBack variable to generate a SQL statement and in the loop it also decrements the value by 1 and appends these SQL statements into another Collection Variable. After the Until block runs I have an array with 12 SQL statements, which is then used in a ForEach block to execute those 12 statements and dynamically generate 12 files that are Store in a Data Lake. This all works fine and does exactly what I want to do.

Now I have a new requirement where instead of having 12 SQL statements, what I want to be able to do is set the MonthsBack variable equal to some value in the SQL database, so ideally I would create a new DataSet that would query and retrieve this value from the database and then use that to set default value for the MonthsBack database. I can't seem to figure out how to accomplish this with the given tasks.

I know that I have seen in the Dynamic Content pane a way to reference the result of a previous task, but I can't remember how I got to this before. Can someone either tell me or point me to the correct documentation on how to do this. Thanks

Upvotes: 1

Views: 1317

Answers (1)

Martin Esteban Zurita
Martin Esteban Zurita

Reputation: 3209

What you want to do is achievable using a Lookup activity and capturing its output with a Set Variable activity.

On the lookup activity select any dataset from the database you want, then click on "query" and write the query that brings the value you want, I'll give an example:

select Max(Id) as Var1 from dbo.YourTable

Then, on the Set Variable activity you can capture the Lookup Activity's output with something like:

@activity('LookupActivityName').output.firstRow.Var1

Note that I used Var1 on both the query (on the lookup) and the dynamic content (on the set variable).

After this, the variable will be set with the value you want from the database.

Link the set variable after the Lookup

Hope this helped!

Link to documentation on data factory functions and expressions: https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions

Upvotes: 5

Related Questions