Azure pt.3: Parameterize and trigger

All good things in life consists of 3 parts, and so also this series of blogposts.  In previous blogposts we have been creating the Linked Services in Azure and CTAS’ing files from Azure Data Lake into tables on Azure DWH.  I finished my previous post by advising you to use Azure Data Factory V2 (ADF) as an orchestration tool for your ELT load. This is exactly what we’ll be doing in this post.

In order to keep the ADF procedures small we’ll be reading out a parameter table from an Azure SQL DB containing the names of the Stored Procedures that we want to trigger in ADF.  ADF will then iterate over these parameter lists and execute the Stored Procedures on our Azure Datawarehouse.  The execution can occur sequentially or simultaneously, depending on the settings.

Let’s get started.

What we need as prerequisites

• Your CTAS statements written out and created as a Stored Procedures.
• A parameter table containing an overview of the Stored Procedures you want to run.

In our example the parameter table is called dbo.ParameterTable.  How original 😊

In the Master pipeline you then add a Lookup (you find it under General), and name the lookup Lkp_StoredProcedureList.

Under the Settings tab set the settings as following.

The Source Dataset is the one you just created.  As my parametertable is very simple I just read the entire table into my array.  But you could also load a reduced dataset when you use a query with a filter on a MustRun field for example.  The table can contain several columns, for instance, if you would want to run several stored procedures or U-SQL statements (Azure Data Lake Analytics) in the same iteration of one worker pipeline.

Second step

As we created our lookup it is now time to create exactly that Worker pipeline we just discussed about.  In our example it’s called Workerline_SP.

Under Iteration & Conditionals you’ll find the ForEach object.  Drag this into the Workerline, (deselect the ForEach) and create a Parameter called PipelineList you give the type Object.  I called the ForEach loop Iterate_SPList.

Now you select the Foreach activity Iterate_SPList and set the following settings:

By doing this I force the ForEach loop to run iteratively.  If you unselect it you have to set a batch count for controlling the amount of parallel executions.

The really important setting here is that of the Item.

We populate it with @pipeline().parameters.PipelineList.

This means we will iterate over the looked up array and use its content as parameters for our load.  Remember that in the Workerline parameter settings we have set the object that will be called PipelineList.

Next you click on the pencil in our for each loop.  This will bring you to the payload.  In this example we will trigger our Stored Procedure on the Azure DWH.

So in the payload we drag in the Stored Procedure activity from under General.

I have called it SP Workerline LoadSP.  When you click it go to the section SQL Account, and choose the Linked Service referring to your Azure DWH.

Next you go to Stored Procedure, this is the one we will trigger.

Under Stored procedure name you type in @{item().SP_Name}.

The item() is the reference to the array we have set in the setting of our for each loop.  The SP_Name is the columnname of our parameter table.  So in case you want to run several activities in the same iteration, this is the place where you can define which column from the parameter table you assign to each activity.

If someone at Microsoft is reading this.  I found it hard to discoverer useful documentation about all the parameter options in the Microsoft Documentation, so here’s a warm call to the Microsoft staff to provide an overview of all possible options and possibilities we have using dynamic content in ADF 😊.

Tirth step

Almost there.  We have set up the entire Worker; but in order to get everything working as it should, there is one missing step left.  We still need to trigger the Worker from within the master

Go back to the Master and drag the Execute Pipeline activity in from under General.

Link the both together under the success output.  If the lookup was a success, the pipeline will start, if not nothing will happen and the workflow will fail.  ETL tools anyone?

Click on the Execute Pipeline activity and under Settings set the following.

The Invoked pipeline is the worker pipeline you just created.  With the Wait on completion option set I force the trigger to only report completion when the underlying pipeline finishes.

At parameters we off course need to pass our parameter array to the workerline pipeline.  So I create a parameter PipelineList and give it the following value: @activity(‘Lkp_StoredProcedureList’).output.value

This will send the content of the output of our lookup into the Workerline pipeline.

The proof of the pie is in the eating

Looking at the Monitor we see the runs to be successful.  I have 2 different stored procedures inside my look.  One for ctas’ing real estate and one for insurance data.  They both ran fine.

So we see that both my Master and Workerline started and when I go deeper into the workerline details, I see that my for loop did 2 iterations, and ran both stored procedures in my parameter table.

Off course you are not obliged to store your parameters in an Azure SQL database.  You can also maintain it in an Azure table or JSON file.

Wish you all the best in your future Azure use.