Azure Data Factory - ETL Solution To Azure DB And To Azure SQL Data Warehouse

Introduction

In this article we will learn how to use Azure Data Factory to Extract, Transform, Load (ETL) data especially for the data warehousing purposes.

If you are new to Azure Data warehouse, I would like to suggest the below prerequisites.

Prerequisites

  1. SQL Server on-premises installed
  2. Your Enterprise or Demo database available on SQL Server on-premises
  3. Let's Move 'On-Prem' Data Warehouse To 'Azure SQL Data Warehouse' With Redgate Data Platform Studio (DPS)
  4. An Azure SQL Data Warehouse account, if you still don’t have one please create Azure SQL Data warehouse here

If you have some questions on Azure SQL Data Warehouse such as

Why Enterprises Should Now Consider ASDWH Seriously please read here

This is a real time example to schedule ETL from beginning.

Background

Most recently, I got an opportunity to migrate and schedule ETL job from ‘On-Prem’ DWH to ‘Azure SQL Data Warehouse (ASDWH)’.

Getting Started

I am sure that you already have an Azure SQL Data Warehouse (ASDWH), so please LOGIN there and go to the ASDWH

Azure SQL Data Warehouse

Under Common Task of ‘Azure Data Warehouse’, please click on ‘Load Data’ as shown below:

Azure SQL Data Warehouse

After you click the load data the below screen will open, please click Azure Data Factory:- 

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Click on the Create Button.

Provide the required details, please note currently 2 versions of Azure Data Factory are available

Azure SQL Data Warehouse

However, I will choose V1 – Version 1, V2 Version which is currently in preview will be used in next article as shown below :-

Azure SQL Data Warehouse

Select location nearest to geography as per your business and click Create

Azure SQL Data Warehouse

And Azure Data Factory is ready

Now we need to schedule ‘Azure Data Factory’ to update the ‘Data warehouse database’, so click ‘Copy Data’,

Azure SQL Data Warehouse

Azure SQL Data Warehouse

As you click on the Copy Data, the below screen will open:-

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Please note ‘Task cadence on Task schedule’ has the following options:-

  1. Run once now
  2. Run regularly on schedule

I’m considering my ‘AzureDataFactory’ as ETL to repeat every day at 8:25 PM until end date time of ‘12/31/2099’. If you want to copy only once, similar to ‘Redgate- Data Copy’   as described here Let's Move 'On-Prem' Data Warehouse To 'Azure SQL Data Warehouse' With Redgate Data Platform Studio (DPS)

Click next

Azure SQL Data Warehouse

As my ‘On-Prem’ data warehouse source is SQL Server, so select ‘SQL Server’ as shown above, and click next

Azure SQL Data Warehouse

Here you will be landed to the page and it will ask to Configure Integration.  Click at ‘Launch express setup on this computer’ as shown below,

Azure SQL Data Warehouse

And it will download the gateway as shown below

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

And Click Next

Azure SQL Data Warehouse

Azure SQL Data Warehouse

If no changes are required click Next. As my destination is Azure SQL Data warehouse, I will select it and click next

Azure SQL Data Warehouse

Below screen will appear

Azure SQL Data Warehouse

Below Mapping will be displayed to verify

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Click Next For Final Deployment

Azure SQL Data Warehouse

And with this, you just deployed your first ETL Job- ‘Azure Data Factory’. Just to recall, this job will execute daily. You could change the frequency anytime as per business need.

Thanks again for reading the blog.

In case of any doubt or question, feel free to write message or connect by any social media, I will reply ASAP. You may also reach me at My Website from here.

Until next time, keep learning….