Connecting to Azure DataLake using SSIS

Azure DataLake and SSIS

In this article we will try to move some data from on premise system to Azure DataLake store using SSIS.

Pre-requisite

• Azure subscription with DataLake store created.
• Admin privilege so that we can provide necessary permission to APP or AD user on ADLS. 
• Azure Feature pack installed for SSIS (https://guru-msbi.blogspot.in/2017/12/azure-feature-pack-for-ssis.html)

Scenario

We have DataLake Store created on Azure and we have to move data from on premise SQL Server to Azure.

Solution

You can create a DataLake store on Azure  ( http://guru-msbi.blogspot.in/2018/01/azure-datalake.html ) and provide necessary access.

Get the ADL URI from the datalake store overview.

Steps on SSIS

For moving data create two connection managers for source and destination. SQL Server source will require OLEDB source connection and DataLake will require Azure DataLake connection manager.
Before creating DataLake connection using connection manager provide user Contributor access on AC(IAM) and Read/Write access on Data Explorer.
Now add your required details on ADL connection manager as below.

Once all connection managers created then configure your source and destination accordingly.

The ADLS destination path and file details must be configured correctly as shown below.

Go to mapping tab and map columns and save the package.

You can run your package to see if the data gets loaded in ADLS as shown below.

YouTube Video