Azure Data Factory — a lookup, for each and variables

This post is a continuation (next step) of the previous one. Where we passed a parameter between exterior and interior pipeline.

Azure Data Factory pass a parameter from a parent pipeline to a child pipeline

Today an [InvokeChildPipeline02] child task is under our consideration. As you remember we passed a parameter from a parent pipeline to a child one. We do the same thing for this pipeline and extend a pipeline functionality.

So, we start with a derived parameter from the parent pipeline.

We want to check the parameter value and assign it to a variable. In order to do this, we can use a Set variable activity.

Here is the condition code:

@if(
    greaterOrEquals(
        int(pipeline().parameters.child_pipeline_parameter), 
        50000
    ),
    string(sub(
        int(pipeline().parameters.child_pipeline_parameter), 
        2000
        )
    ),
    string(add(
        int(pipeline().parameters.child_pipeline_parameter), 
        2000
        )
    )
)

Once our variable is set, we can use it as a query parameter in a Lookup activity.

The Lookup activity returns a .json file containing a query result.

Then, for every Lookup activity dataset record we want to insert the record into a SQL table. By using a For each activity, we iterate every record and run a stored procedure.

The stored procedure code:

CREATE PROCEDURE dbo.InsertData
    @Inserted DATETIME2, 
    @Name VARCHAR(50), 
    @Value INT
AS
    INSERT INTO dbo.InsertedData (Inserted, Name, Value) 
    VALUES (@Inserted, @Name, @Value)

Inside the mentioned For each activity is a Stored procedure activity placed.

As you see, the second and the third stored procedure parameters are a @item().County and a @item().Population items. They are single “row” items returned by the For each activity loops run.

The pipeline run shows that the variable has been set, the Lookup activity found two records, then the For each activity run the Stored procedure activity two times.