In previous post you’ve seen how to create Azure Data Factory. In this post we want to take the first step in building components of Azure Data Factory. Usually the very first step is creating Linked Services. Linked Services are connection to data sources and destinations. Data Source or destination may be on Azure (such as Azure Blob Storage, Azure SQL Database) or on premises (such as on-premises SQL Server, or on-premises Oracle). Linked Services need to work with Data Management Gateway if the data source/destination is on-premises.

In this example we follow the previous post solution; We want to copy data from some CSV files exists on Azure Blob Storage and load it into Azure SQL database. So we need two Linked Services for this example; one for Azure Blob Storage, and the other one for Azure SQL Database. Creating Linked Services might not be so hard once you have the environment ready for it. However in this example as we want to do everything from the scratch I’ll explain you how to create an Azure Blob Storage and upload CSV files there to be the source of our operation. I’ll also explain how to create the destination table in Azure SQL Database.

Prerequisite Step 1: Create Azure Blob Storage

Azure Blob Storage is self-explanatory; an storage on Azure where we can put Blob files. When you have an Azure Subscription then you can have one or more storage accounts associated to it. For this example I want to create an storage account. So I open the new Azure Portal, and I click on New, then under Data + Storage, click on Storage.

Name the New Storage as you want, I named it for this example radacadstorageadf (note that name only should contain lowercase letters and digits.

Set the Resource Group to the resource group we’ve created in previous example: RADACAD-ADF

set other options as you want. Click on Create to create the storage account. Once creation of storage account has been completed you will be redirected to it.

Storage account in very simple explanation is like a hard drive that you can store files (blob), tables, and other stuff. For this example we want to store Blob files (CSV Files) so we create a container for it. a Container in simple word is like a shared folder.

Click on Container and name it as customer-csvs (or anything else you like), then click OK.

After creation of the container you will see that under the list of containers.

Uploading CSV files into the Container

There are two ways of upload files into a Storage Account Container (at the time of writing this blog post);

• Through Azure PowerShell
• With utility tools such as Azure Storage Explorer

The second method is easier and require less work with scripting, so we go ahead with that. Download the latest Azure Storage Explorer version from CodePlex here:

https://azurestorageexplorer.codeplex.com/

after installing this tool, open it. Set the Storage Account information by clicking on Add Account button.

For adding the storage account you will need the storage account name (the name that previously you have chosen for your new storage account), and the account key.

Follow these steps to copy the account key;

On Azure Portal click on Browse All, then click on storage accounts, and then the account that you’ve created before.

In Storage Account panel, click on the Key icon on the top right hand side, and when all keys appears, click on the copy button besides Primary Access Key.

Then paste it in Azure Storage Explorer Add Account window.

Now you can see the Storage account and customer-csvs container. you can upload files as you want into this folder.

Note that we won’t upload files into this folder yet, as that step will be done later in the next example.

Prerequisite Step 2: Create Azure SQL Database

Destination in our example is an Azure SQL Database, So let’s create a new database in Azure Portal for it.

Open Azure Portal, Click on New, and under Data + Storage, click on SQL Database

Create a new server for the SQL Database, set name of the server and admin login and password as you want.

Name the SQL Database as you want, I named it as: adf-basic-sample . set other options as well as the resource group and click on Create.

After creating the database you can test the connection to it through SQL Server Management Studio. your management studio should support connection to Azure. Here are steps that you require to connect to Azure SQL Database from SSMS;

In SSMS connect to Database engine and set the server name as the name of Azure SQL Server. Change the authentication mode to SQL Server Authentication, and set admin username and password.

If you faced below error message it means that  your IP address is not allowed to access this server, and you have to configure the server firewall to bypass your client IP address. So just copy your client IP address (highlighted below), and paste it in the address as I mention in following;

Error: Cannot open server …. requested by the login. client with IP address …. is not allowed to access the server…

In Azure Portal click on Browse All, then from SQL Servers, choose your server name, and click on it.

in SQL Server Page, click on Show Firewall Settings, and set a new Rule for your client IP address. Save the work.

Now try again to connect from SSMS to Azure SQL Server (Please note that the change may take few minutes to allow your client IP through firewall, so be patient)

We don’t create destination table structure in this post, we’ll keep that for the next post when we discuss Datasets.

Creating Linked Services: Azure Blob Storage

Now is the time to create Linked Service that connects to our data source. our data source is Azure Blob Storage and is the container in the storage account that we’ve created in Prerequisite step 1. So let’s create Linked Service for it.

1- In Azure Portal, click on RADACAD-Simple-Copy Data Factory that we’ve created in previous post.

2- Click on Linked Services, and then click on New Data Store Icon

3- Name the Data Store as Azure Blob Customer CSV

4- set the Type as Azure Storage (As you can see in image below image good range of data sources are supported in Azure Data Factory)

5- set the account name and account Key (You know from Prerequisite Step 1 of how to find account key and copy it)

6- Create the Linked Services.

If there be any issues you will see an error message at the time of creation of Linked Services or in Notifications area.

Creating Linked Services: Azure SQL Database

We should create another Linked Service for Azure SQL Database. follow the first two steps from previous section, and then;

1- Name the Data Store as Azure Customer SQL Database

2- Set the type as Azure SQL Database

3- set the server name, database name and authentication information (you know these from Prerequisite Step 2)

4- Create the Linked Service.

Now you should see two data stores under Linked Services sections of your Azure Data Factory.

In the next post we will create two datasets for these Linked Services.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.