Simultaneously Loading Multiple CSV files to Different Destination Table

Introduction

In this blog post, we are trying to load a bunch of CSV file from Azure Blob Storage to Different Destination Table of Azure SQL Server. Here we are not using any one to one mapping. All activities must be done in a single pipeline and single Copy Data Activity.

Hope it will be interesting.

Case Scenario

In our Azure Blob storage, we have different CSV file with different metadata. We need to store them in our Azure SQL Server table. One file is for one table. The table is created automatically, so there is no column mapping over here.

Source Azure Blob Files

 


ADF Solution

 

  

First we need two Data Sets. One for retrieving all CSV files information from Blob Storage and Second is for copy the each file contain in destination. With this destination Data sets create table automatically for each CSV file.

Data Sets-1 (Used to Retrieve all File From Blob Storage)

 

 



Data Sets-2 (Destination Data Sets for SQL Table Creation and Storing CSV Data)

 


 

Get Meta Data Activity

 


 

ForEach Loop Activity

 


 

Copy Data Activity

It is inside the ForEach Loop activity

 


 

Output

 


 

Consideration

If we check the Table definition, all the columns have data type nVARCHAR(max)

 


 

 

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

Triggering Pipeline in ADF

Image
Introduction Pipeline is ready and we already publish it, now we need to trigger our pipeline in a schedule interval. For SSIS Developer, we have SQL Server Agent job to schedule and run the SSIS Project or Package.   Scheduling Jobs in SQL agent have some limitation. We have to logically check the SSIS Package execution is completed or not before proceeding to next run of same or dependent project. It also has some flexibility by using Steps. Suppose we need to run a SQL Script on Step-1, then SSIS package on Step-2. We are trying to say that dependent activity can run step by step. In Azure ADF triggering, we have some options that we not find in SQL Agent Job, so both have some advantage and Limitation also. In this blog post we are going to discuss ADF Add Trigger options. Hope it will be interesting.   Where we get the Add Trigger     When we open the Pipeline we can get the Add Trigger button beside the Debug button. After clicking this butt...
Read more