Problem: You have text files in csv or AVRO formats and you would like to upload them to Blob storage as intermediate steps of big data processing in Azure. How to upload them to Azure Blob storage?
Solution:
Use "Azure Blob Upload Task" or "Azure Blob Destination" in Data Flow Task in Integration Service in SQL Server (SSIS).
Before we open new Integration Service project in Visual Studio 2015, you need to do preparations to get our environments setup:
- Sign up Azure account.
- Install Azure feature pack for SQL Server and ensure that Azure tools appear in Visual Studio. If Azure tools are grey out, please refer to the article to resolve issues.
- Install Microsoft Azure Storage Explorer and create a storage account.
After you create a storage account, you should see four types of storage appearing under your storage account at Azure Storage Explorer:
Then you can create a Blob container to contain the file to be uploaded. You can easily do it by using Microsoft Azure Storage Explore shown below. Here the Blob Container called "ssisdemoblob" is created and under this a Blob Directory "data" is also created. The summary of naming rules for Azure can be referred as this article.
Now you can go ahead to open visual studio and create a new SSIS project.
Step 1: Create Azure Storage Connection.
- Choose "AzureStorage" as Connection manager type.
- For Account Key in "Azure Storage Connection" editor, copy "Access Key" in your Azure account.
- Click "Test Connection" to make sure it connects successfully to your Azure account.
Below shows how to get access key for your storage account from Azure Portal.
Note that you can create Azure Storage Connection under project level connections as shown below so every package under this project can share the same Azure Storage connection.
Step 2: Configure the Azure Blob Upload Task in SSIS as shown below:
To make this upload more dynamic, here we use variable @[User::srcFolder] for local directory. You can use expression for other properties such as BlobContainer, BlobDirectory, Disable.
Moreover, you can use wildcard for FileName as in other SSIS solutions.
After executing this task, you can see the related file appears under the Blob Directory specified. Now you have your first file uploaded to Azure Blob Storage!
Note that you can also use "Azure Blob Destination" in Data Flow Task to achieve this.
Azure Blob Upload Task can be easily used to upload one more files to azure Blob storage by using wildcards for FileName. While for "Azure Blob Destination" in Data Flow Task, you have to use Foreach Loop Container together with Azure Blob Destination to load multiple files to Blob Storage.
Moreover, you can use wildcard for FileName as in other SSIS solutions.
After executing this task, you can see the related file appears under the Blob Directory specified. Now you have your first file uploaded to Azure Blob Storage!
Note that you can also use "Azure Blob Destination" in Data Flow Task to achieve this.
Azure Blob Upload Task can be easily used to upload one more files to azure Blob storage by using wildcards for FileName. While for "Azure Blob Destination" in Data Flow Task, you have to use Foreach Loop Container together with Azure Blob Destination to load multiple files to Blob Storage.
