Nisarg Upadhyay
SSIS data flow task

Uploading SQL data into Azure Blob Storage using SSIS

August 20, 2020 by

In this article, we will learn to upload the excel file to Azure blob storage using SQL Server Integration Service Package. The excel file contains the output of a T-SQL query. This article is a small demonstration that gives some idea about the Azure Blob Upload task and how it can be used to upload files using SSIS.

To upload the file on the Azure blob storage container, we will use the Azure Blob Upload Task of SSIS. To demonstrate the process, I have prepared a setup on my workstation and azure account. The details are the following:

  1. I have restored a backup of the AdverntureWorks2017 database on the SQL Server instance
  2. I have created an excel file named employees.xls. The output of the query will be exported to the employee.xls file
  3. I have created an Azure Blob storage account and a container on Azure. We are going to use the Azure Blob Upload Task

Let us create an SSIS package to copy the data of the On-premise SQL Server to the excel file. We will create a new SSIS project. To do that, Open SQL Server data tools -> Click on File -> Hover on New -> click on Project. In the new project dialog box, select the Integration Services Project. Provide the desired name and click OK. See the following:

New SSIS Project

First, let us configure the data flow task.

Create a data flow task to copy output of the T-SQL query to excel file

Drag and drop the data flow task on the control flow tab and provide the appropriate name. Double-click on the dataflow task to configure it. See the following image:

Add data flow task

On data flow task window, drag and drop ADO.Net Source and Excel destination from SSIS toolbox. See the following image:

Add ADO.NET source and Excel destination

First, let us configure the ADO.NET connection—double-click on it. ADO.NET Source editor (Image:1) opens. Click on New. Dialog box Configure ADO.NET Connection Manager (Image 2) opens. Click on New. See the following image:

Create ADO.Net connection

A dialog box Connection Manager opens. Provide the following information:

  1. Server name: Select the name of the server on which SQL Server is hosted
  2. Authentication: Provide authentication methods. If you are using SQL Server authentication, then provide Username and Password
  3. Database Name: Select database name from Select or enter database name drop-down box

Click OK to save the ADO.NET connection and close the dialog box. See the following image:

Configure SQL Server connection

On the ADO.NET source editor screen, select Table or view from the Data access mode drop-down box. Choose the desired Table or view name. Click OK to save the connection properties and close the window. See the following image:

Choose database view

To configure the excel destination, first, connect source and Destination. To do that, drag and drop the blue arrow on an Excel destination. See the following snapshot:

Connect to source and destination

Double-click on excel destination to configure it. On the Excel Destination Editor dialog box, click on New. A dialog box Excel Connection Manager opens. In the dialog box, provide the path of the excel file. Click OK to close the dialog box.

Configure Excel connection

On Excel Destination Editor, choose Table or view from Data access mode drop-down box. Select the name of the excel workbook from the Name of the Excel sheet drop-down box. See the following image:

Choose data access method and name of excel sheet

To map the columns of the ADO.Net source and Excel destination. Click on Mappings. The column name of SQL View and Excel file is the same so that mapping will be performed automatically.

ADO.Net source and excel destination mapping

Click OK to close the Excel Destination Editor. The data flow task looks like the following image:

SSIS data flow task

To test the data flow task, click on Execute. The SSIS package has been executed successfully, and data have been written to the excel file. Now, let us configure the Azure Blob Upload task.

Configuring the Azure Blob Upload Task

To configure the Azure Blob Upload task, drag and drop it from the SSIS toolbox to the Control Flow window. We want to upload the excel file to the blob storage container, hence first, connect the Data flow task and Azure Blob Upload task. See the following image:

Drag and drop azure blob upload task in control flow window to configure Azure blob storage

Double-click on the Azure Blob Upload task. Azure Blob Upload Task Editor opens. In the dialog box, we will create an Azure Storage connection and Local Directory. First, let us configure the Azure Storage connection. Click on AzureStorageConnection drop-down box and select New Connection. See the following image:

Azure Blob Upload task editor to connect to Azure Blob Storage

A dialog box Azure Storage Connection Manager Editor opens. Configure the following image:

  1. Service: Select the Azure storage service which you want to use. The options are the following:

    1. Blob Storage
    2. Blob Storage (Emulated)
    3. Data Lake Storage Gen 2

    We will use Blob Storage, so select Blob Storage.

  2. Account Name: Enter the Azure storage account name.
  3. Authentication: The authentication method that you want to use to connect to the storage account. Options are the following:

    1. Access Key
    2. Service Principal

We are going to use an Access key, so choose Access Key.

Once Parameters configured, click OK to close the dialog box.

Properties of Azure Blob Storage

In Destination, provide the Name of the BlobContainer. In LocalDirectory, provide the name of the directory where the excel file is created. Click OK to close the Azure Blob Upload Task Editor dialog box. See the following image:

Configure Azure Blob Upload task to upload the file on Azure blob storage

Once an SSIS package is configured, it looks like the following image:

SSIS Package

To test the SSIS package, click on Execute.

SSIS package executed successfully

As you can see, the package has been executed successfully. Now, to verify that file has been uploaded on Blob container. Log in to the Azure Portal Navigate to the Storage account Open container. See the following image:

File uploaded to the Azure Blob storage container

As you can see, the excel file employee_data.xls has been uploaded on the Blob container.

Summary

In this article, we have learned how we can configure the Azure Blob Upload task in SQL Server Integration Services to upload the output of a SQL Query that is stored in an excel file on an Azure Blob Storage container.

Nisarg Upadhyay
Azure, ETL, Integration Services (SSIS)

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

168 Views