Copying Multiple File from Blob Storage to Single SQL Table

Introduction

This blog is for SSIS people, who want to move to ADF v2.

Here we are using a simple case scenario like SSIS and continuing to extend the solution in my coming blog post. Here we assume that the readers has basic understanding with ADF.

Hope it will be interesting.

Case Scenario

The case scenario is most common for SSIS developer.

I have a Blob Storage and there are two txt file with same metadata.  I need to load it an Azure SQL Server Table (Both the file).

 Source Files in Azure Blob Storage

ADF Solution

Lookup Activity Configuration

Here I am using a lookup to extract data from a table that have table name that need to be load from Azure Blob storage to Azure SQL Table object.

SELECT FileNames

FROM   LookupTab

WHERE  NeedLoad = 'Y'

 

There is a property in Lookup named First Row Only

We need to unchecked it as we need all the records that is return by our SQL statement.

ForEach Activity Configuration

The output that we find from the Lookup need to be Input of ForEach like a Object variable in SSIS.

Here we use

 @activity('FileToLoad').output.value

Here the name of our Lookup Activity is “FileToLoad”

 

 

We are using a Copy Data Activity under ForEach container to copy data from Source Blob to Destination SQL Table.

 Copy Data Activity Configuration

 Before move to Copy Data Activity, we have to dynamically configure the Data Set file name.

 Data Sets Property

 Here in Data sets we are using parameters named “FileName”

 In the Connection tab we are dynamically define the file name within the File Path property 

@dataset().FileName

 

 Copy Data Activity Property

 In Source Tab to catch the value from ForEach, we use

 @item().FileNames

 

In Sync Tab we define the data sets of Destination

 

In Mapping Tab we just map source to destination with proper data type

 

 

 

Output

 

 

Hope you like it.