Bulk load/write data from SQL server to Azure Table Storage using SSIS

Question

I would like to copy SQL Server data to Azure Table storage. We can achieve this task using Azure Data Factory. However, I wanted to do this with SSIS package.

I installed feature package for Azure but could not get the Azure Table Storage as destination in SSIS Toolbox.

Is there any other simple way to achieve this?

Answer 1

There are a couple of things that we need to take care of before we walk through building the on-premise SSIS packages:

-Create Azure Subscription

-Create Azure Storage Account

-Create Azure SQL Database

-Install SQL Server Data Tools (SSDT)

-Install Azure Feature Pack for SQL Server Integration Services (SSIS)

-Install SQL Server Management Studio (SSMS)

I think you are missing the Azure Feature Pack for SSIS which includes a handful of components for working with Azure from SSIS.

EDIT 1 :

It seems that Azure Table Storage Source is a third-party component.

EDIT 2 : You can use SSMS : Right-click on the database and select Tasks-Export Data.

Then choose Destination as Flat File Destination and specify a CSV path as the destination.

Choose "Write a query to specify the data to transfer"

Start Azure Storage Explorer, open the target table which the data would be imported into, and click Import on the toolbar.

Select the file just exported, check and change the data type if necessary for each field.

Click Insert, and then your data will be imported to Azure Storage Table.

You can also verify the data on Azure Portal.