Load Data from CVS File to Azure SQL database Using Azure Data Factory

Here we have 2 csv files in BLOB container, and we want to upload data from csv file to respective database tables is (e.g. Product file data should be loaded in Product table).

There are some prerequisite: You should have an Azure subscription. There should be a BLOB container under storage account which has the source file. You should have already set up an Azure SQL database server. and you should have SQL Server detail, User id, Password and should have access to it.

First step is to read the file names from Blob container. We are going to use GetFileName Activity in ADF. Create a new pipeline and drag GetFileName activity.

In dataset tab, select the dataset for BLOB from dropdown.

Here we have an option for argument down below, which we can configure based on what data meta data we want to read.

Here we will select Child Items from drop down menu. Click on validate, once it is successful click on debug, it will show you the name of files present in source container.

Now we will iterate through with the files present in source folders (BLOB Container). To achieve this, we are going to user the ForEach activity. Drag it in pipeline and connect the output of Get Metadata activity with it.

Now click inside ForEach activity and go to Settings tab. In Item field, click on add dynamic content and add below expression and click finish.

Here we are passing the filename from get metadata activity.

Go to activity tab and add new activity. It will take you inside loop. Drag If Condition activity here.

We will check the file name in this step. Go to activities tab, Expression field click on add dynamic content and add below expression and click on finish.

Here we are passing expression to check if File name starts with Product.

Now we need to load the data from Product file to Product table. Click on pencil sign of TRUE activity. It will take you inside the activity.

Drag Copy activity inside If condition activity.

Click on source tab, select the dataset from drop down which is pointing to the source folder in BLOB container. Select wildcard in file type path.

Now click on add dynamic content in wildcard File name and give below expression.

It will pick up the file which starts with Product.

Go to sync tab and select the dataset from dropdown (It should be a parameterized).

Once you select, it will ask for schema name and table name.

Now go to mapping tab and click on import schema. It will import the column from both source and destination.

Verify the mapping and click on validate and debug it

Once it is successful. Validate your pipe and publish it.