Azure Blob Storage Data Upload with SSIS


By:   |   Updated: 2020-12-16   |   Comments   |   Related: More > Azure


Moving SQL Server workload to the cloud

Free MSSQLTips Webinar: Moving SQL Server workload to the cloud

Attend this session to learn how managing performance is even more critical with Azure SQL DB and Azure Managed Instance. Learn what key performance indicators are most important, what auto-tuning really means and get some tools to help you identify performance issues and correctly size your database.


Problem

Azure provides a cloud solution for storing data using Azure Blob Storage. We need to export SQL Server data and store it in Azure blob storage. How can we do so? This tip will cover the following topics.

  • A brief overview of Azure storage.
  • Setup Azure storage account, containers.
  • Configure an SSIS package for data upload into the blob storage.
  • Download the data from blob storage into the local storage.
Solution

Azure Blob Storage Overview

Azure Storage provides a scalable, reliable, secure and highly available object storage for various kinds of data. You get the following kinds of data storage:

  • Azure Blobs: An object-level storage solution similar to the AWS S3 buckets. You can store the file and access it through a URL. It can be used for streaming audio, video, documents, binary log files, images, etc.
  • Azure Files: Use this to configure the file shares for on-premises or the cloud deployments.
  • Azure Queues: Store a large number of messages for communication between application components.
  • Azure tables: Azure tables can store the structured NoSQL data in the cloud. It is suitable for structured and non-relational data.
  • Azure disks: Azure disks are used as storage volumes for the Azure Virtual machines.

In this tip, we are working with the Azure Blobs for storing the exported data from SQL Server.

Azure Storage Account

Log in to the Azure Portal by specifying your Azure subscription credentials. If you do not have access, use this link to Create your Azure free account.

Once you login to the Azure portal, a dashboard is launched for all Azure services.

Azure Storage Account

Click on the Storage accounts under the Azure services. As shown below I do not have a configured storage account in my subscription.

Azure services

Click on Create storage account. In the below storage account configuration, enter the following values.

  • Resource group: A resource group is an Azure container for Azure resources. Choose an existing resource group or create a new group. Here, I use the new resource group – StorageAccountResourceGroup.
Create storage account
  • Storage account name: Enter a unique storage account name.
  • Location: Select the Azure region from the drop-down. You should choose the nearest region to avoid network latency.

We can go ahead with default values for the other configurations.

Storage account name

First, validates all configurations for the storage account. Once the validations pass, select Create.

validates all configurations

Next, the deployment will start for the storage account.

deployment for the storage account

A storage account is created quickly. Click on Go to resource to open the storage account.

Go to resource to open the storage account

In the storage account you can see storage options as described earlier.

storage options

Create a Container in the storage account

We need to create a container for blob storage. Select "Containers". If there are any existing containers you can view them or create a new container.

Create a Container in the storage account

Select this "Container" and specify a name for your container. Here, we specify the container name datauploadssis.

specify a name for your Container

Our container datauploadssis now exists in the mssqltipsdata storage account.

storage account with container

Install Azure Storage Explorer

The Azure Storage Explorer tool manages the storage account and works with Azure blobs, files, queues, tables, Azure Cosmos DB and Azure Data Lake Storage entities.

Launch the Azure Storage Explorer and choose the option Use a storage account name and the key to connect to the storage account.

Install Azure Storage Explorer

To connect with the storage account, use the access keys. You can click on the access keys option and it shows the following information:

  • Storage account name
  • Access keys
access keys

Note the access keys and enter the next screen to connect to the storage account.

access keys

Click Next. Review your storage account name, display name and access key.

Review your storage account

Click on Connect. Here, we see the storage account and Container.

storage account and Container with directory

We can create a new directory in the Container for uploading files. Click on New Folder shown above and enter a folder name as shown below.

create a new directory

In the Azure storage explorer you can view the connected storage account, container (datauploadssis) and directory (Mydata).

View Directory

Download Azure Feature Pack for Integration Services

We need to install the Azure feature pack for Integration Services to work with the Azure resources in the SSIS package. Before you install the Azure feature pack, make sure to have the following environment for this article.

Download Azure feature pack for the Integration Services

Download and install the SSIS feature pack as per your integration service version.

Create a new SSIS Package for Azure Blob Storage Upload

Launch Visual Studio 2019 and create a new Integration Services Project.

Create a new SSIS package for Azure Blob Upload

Specify a project name and directory to store the package files.

Specify a project name

In the SSIS toolbox we see the Azure blob tasks as highlighted in the square box.

the Azure blob tasks

To export the data from a SQL database table, add a data flow task.

SQL database table

In the data flow task, add an OLE DB Source (renamed as Source SQL Data) and destination (renamed as Destination CSV). We will not discuss in further detail the configuration of these OLE DB sources and destinations in this tip. You can follow these integration services tips for detailed steps.

the data flow task

For reference purposes, my OLE DB source details are as below:

  • SQL instance: SQLNode2\INST1
  • Source Database: AdventureWorks2019
  • Table for export: dbo.orders
OLE DB source details

The configuration for the Flat file destination is as below:

  • Flat file format: CSV
  • File name: C:\Test\SampleDataUpload.CSV
Flat file destination

Add the Azure Blob Upload Task and join it with the Data flow task as shown below.

Azure Blob Upload Task

We need to configure the Azure Blob Upload task. In the task editor, perform the following configurations.

Azure Blob Upload Task configuration

AzureStorageConnection: Add a new storage connection by specifying the storage account name, authentication (access keys), and account key (access key).

AzureStorageConnection

Select "Test Connection" to verify the connection with the storage account.

Test connection

Once the storage account connection is successful, enter the blob container and directory as we configured earlier.

In the source folder, enter the path for the CSV file. This is the output of the data flow task.

blob container and directory

Click OK to finish the configuration. Execute the SSIS package and it should run successfully.

execute the SSIS package

Refresh the Azure Storage container and you should see the uploaded CSV using the SSIS Package.

Refresh the Azure Storage container

You can view this using the Azure Storage Explorer.

the Azure Storage Explorer

SSIS Package for Azure Blob Storage Download

Similar to the Azure Blob Upload Task, use Azure Blob Download Task for downloading files from Azure blob storage as shown below.

In the below package, we also use an Execute SQL task in between the Azure Blob Upload Task and Azure Blob Download Task.

Azure Blob Upload Task

This Execute SQL task is to introduce a wait between a blob upload and download. If you have a large file to upload into the storage container, it might take some time depending upon the network bandwidth. Therefore, the wait might solve the issue if you want to download the same file.

Below is the configuration for the Execute SQL task.

Delay using SQL

Below is the configuration for the Azure Blob Download task.

configuration for the Azure Blob Download task

Execute the SSIS package and see all tasks completed successfully.

Package execution
Next Steps


Last Updated: 2020-12-16


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Process Blob Files Automatically using an Azure Function with Blob Trigger

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks














get free sql tips
agree to terms