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:

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:

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

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:

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:

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:

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:

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.

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:

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.

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

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.

#azure #etl #integration services (ssis) #sql