Using the ForEach and Filter activities
In this recipe, we introduce you to the Filter and ForEach activities. We shall enhance the pipeline from the previous recipe to not just examine the data in the Azure Storage container, but filter it based on the file type and then record the last modified date for every .csv file in the folder.
Getting ready
The preparation steps are the same as for the previous recipe. We shall be reusing the pipeline from the Using Metadata and Stored Procedure activities recipe, so if you did not go through the steps then, do so now.
How to do it…
- Clone the pipeline from the previous recipe and rename it
pl_orchestration_recipe_3. - Delete the Stored Procedure activity.
- Select the Metadata activity and configure it in the following way:
(a) In the Dataset tab, verify that
CsvDataFolderis selected as the dataset.(b) Verify that the Item Name and Last Modified fields are added as arguments. Add one more field, Child Items.
- Now, select a Filter activity from the Activities pane on the left (find it in the Iteration and Conditionals section) and drop it in the pipeline canvas to the right of the Metadata activity.
- Connect the Metadata activity to the Filter activity.
- Configure the Filter Activity in the following way:
(a) In the General tab, change the name to
FilterOnCsv.(b) In the Settings tab, fill in the values as follows:
Items:
@activity('CsvDataFolder Metadata').output.childItemsCondition:
@endswith(item().name, '.csv'):
Figure 2.19 – Filter activity settings
- Run this pipeline in Debug mode:
Figure 2.20 – Pipeline status overview in Debug mode
After the pipeline is finished running, hover over the row representing the Get Metadata activity run in the Output pane and examine the activity's output. You should see that the Get Metadata activity fetched the metadata for all the files in the folder, as follows:
Figure 2.21 – Get Metadata activity output
Do the same for the FilterOnCSV activity and verify that the outputs were filtered to only the
csvfiles. - From the Activities pane, add an instance of the ForEach activity on the canvas, connect it to the FilterOnCsv activity, and configure it in the following way:
(a) In the Settings tab, enter the following value in the Items textbox:
@activity('FilterOnCSV').output.Value.(b) Within the ForEach activity square, click on the pencil image. This will open another canvas. We shall configure the actions for the ForEach activity within this canvas.
- Add an instance of Get Metadata Activity onto the ForEach Activity canvas, and configure it in the following way:
(a) In the General tab, change the name to
ForEach Metadata.(b) In the Dataset tab, specify
CsvData(the parameterized dataset we created in the Using parameters and built-in functions recipe) as the dataset for this activity. If you do not have this dataset, please refer to the Using parameters and built-in functions recipe to see how to create a parameterized dataset.(c) For the filename parameter, enter
@item().name.(d) In the same Dataset tab, in the Field List section, add two arguments: Item Name and Last Modified Date, as shown in the following screenshot:
Figure 2.22 – Adding arguments in the Field List section
- Add an instance of Stored Procedure Activity onto the ForEach Activity canvas. Connect ForEach Metadata to Stored Procedure Activity and configure Stored Procedure Activity:
(a) In the Settings tab at the bottom, select AzureSQLDatabase as the linked service and [dbo][InsertFileMetadata] as the stored procedure name.
(b) Click on Import under Stored Procedure Parameters and enter the following values:
FileName:
@{item().name}ModifiedAt:
@convertFromUtc(activity('ForEach Metadata').output.lastModified,'Pacific Standard Time')UpdatedAt:
@convertFromUtc(utcnow(), 'Pacific Standard Time')(you can use your own time zone here, as well):
Figure 2.23 – Stored Procedure activity configuration
Run your whole pipeline in Debug mode. When it is finished, you should see the two additional rows in your FileMetadata table (in Azure SQL Database) for the last modified date for
airlines.csvandcountries.csv. - Publish your pipeline to save the changes.
How it works…
In this recipe, we used the Metadata activity again and took advantage of the childItems option to retrieve information about the folder. After this, we filtered the output to restrict processing to CSV files only with the help of the Filter activity.
Next, we needed to select only the CSV files from the folder for further processing. For this, we added a Filter activity. Using @activity('Get Metadata').output.childItems, we specified that the Filter activity's input is the metadata of all the files inside the folder. We configured the Filter activity's condition to only keep files whose name ends with csv (the built-in endswith function gave us a convenient way to do this).
Finally, in order to process each file separately, we used the ForEach activity, which we used in step 6. ForEach is what is called a compound activity, because it contains a group of activities that are performed on each of the items in a loop. We configured the Filter activity to take as input the filtered file list (the output of the Filter activity), and in steps 7 and 8, we designed the sequence of actions that we want to have performed on each of the files. We used a second instance of the Metadata activity for this sub-pipeline and configured it to retrieve information about a particular file. To accomplish this, we configured it with the parameterized CsvData dataset and specified the filename. In order to refer to the file, we used the built-in formula @item (which provides a reference to the current file in the ForEach loop) and indicated that we need the name property of that object.
The configuration of the Stored Procedure activity is similar to the previous step. In order to provide the filename for the Stored Procedure parameters, we again referred to the provided current object reference, @item. We could have also used @activity('ForEach Metadata').output.itemName, as we did in the previous recipe.