Copying Multiple File from Blob Storage to Single SQL Table – Part-2

Introduction

This is the extension of my proves blog post.

If you not go with previous, I strongly recommended you to read my previous blog post named “Copying Multiple File from Blob Storage to Single SQL Table”.

Case Scenario

Here in this project, we are going to retrieve the CSV file from Azure Blob Storage, but we must retrieve the current date file only and store it into a Azure SQL DB Table. For SSIS developer it is work of using Expression, here I am also using the expression but in different ways that supports by Azure ADF.

Source Files in Azure Blob Storage

We have 3 file in our Azure Blob Storage. But only one file have Modified Date 30/5/2020. So, we need to retrieve that file only which matches the current system date.

 


ADF Solution

 Step-1 We are using Get MetaData  Activity which retrieve all file information from Blob Storage and send it to the ForEach Look container Activity.

Step-2 Within the ForEach Loop Activity we used another Get MetaData Activity to catch single file from Blob Storage and by using IF Condition Activity we checked the Modified date of the file is matched with current system date or not

Step-3 if the modified date matched then we take the file and copy the contents of the file to our destination Table object.


Step-1

Get MetaData Activity Configuration

Before configuring the Get MetaData  Activity we have to prepare our data sets, so that it can retrieve all the file information from Azure Blob Storage.


Now, we need to configure the Get MetaData Activity with the Data Sets that we already configure.


ForEach Activity Configuration

Here we use: 

@activity('Get Metadata1').output.childitems

Hope you remember in our previous article we use .Value, but here we use ,child items as because the list coming from Get Metadata Activity not from Lookup.

@activity('Get Metadata1').output.childitems  --- From Get MetaData Activity

@activity('Get Metadata1').output.value  --- From Lookup Activity

Step-2

Now we move within the ForEach Loop Activity

Get MetaData Activity Configuration

This is our second Get MetaData Activity. Before that we need to configure another Data sets mentioned bellow.


Now we are going to configure our second Get MetaData Activity.


Here we need to properties of each file from Azure Blob Storage. Item Name is used to understand which file need to copy. Last modified is used to understand the modified Date Time of the specified file.

IF Condition Activity

Here in the IF Condition Activity, we are going to compare the Last Modified property value with current UTC date. If it succeed that we copy that file others not.


Here in the Expression of IF Condition Activity we used it to compare File Modification Date with current UTC date.

@equals(formatDateTime(activity('FileDetails').output.lastModified,'yyyyMMdd'),formatDateTime(utcnow(),'yyyyMMdd'))

The expression is self explanatory, hope you understand that.


Step-3

Within the IF Condition True value, we are using Copy Data activity to copy the desired file (if the Modified Date matched with current system Date) to destination Table object.

Copy Data Activity Configuration


Here we create other data sets with parameters SelectedFileName.

The expression is

@activity('FileDetails').output.itemName

We need to understand the Expression.

@activity(‘FileDetails’) --- The name of the Get MetaData Activity that we used within the ForEach Loop Activity.

ItemName – the property that is retrieve by Get MetaData. Here it is file name. 

Output


Related Blog Post Reference

Copying Multiple File from Blob Storage to Single SQL

 

Hope you like it.


Comments

Post a Comment

Popular Posts

Working with Python -- File Handling

Image
Introduction File Handling is a very important portion for any application. In Python, we have several function to Create, Read, Update and Delete file. In this blog post, we are trying to discuss about Python File handling. Hope it will be interesting. Open Function This is the key function to working with file. The Open function takes two parameters. One is File name and second is mode. "r"   -  Read   - Default value. Opens a file for reading, error if the file does not exist "a"  -  Append  - Opens a file for appending, creates the file if it does not exist "w"  -  Write  - Opens a file for writing, creates the file if it does not exist "x"  -  Create  - Creates the specified file, returns an error if the file exists In addition of that, we can specify that the file can be treated as Binary or as Text file. "t"  -  Text  - Default value. Text mode "b"  -  Binary  - Binary mode (e.g. images) Exa...
Read more

Incremental Load by using ADF

Image
Introduction lot of my friend from different group is asking to provide a blog post related to incremental load. If you search Google related to this tropics, you can find bundle of blog post. All are the same using water mark technique. But in my view point it is not a practical approach for incremental load. When we talk about incremental load, we assume that we have billions of records in source which is ever changing and we need to move on destination table. Here in this demonstration we used ADF control flow but it can be done with any ETL or ELT tools like SSIS. One last thing that I would like to mention if we try to load huge records from source to designation, it definitely gives us performance problem or stuck in between. So, we have to load the data in a smaller chunk or batch. We have to keep in track that data that we already loaded in the destination is going to updated in source or not. If it is going too updated, we have to update the destination accordingly. Yo...
Read more