The below blog content is on how do we access azure storage containers from SSIS packages and a demo on copying a file from an azure blob container to another one within the same storage account.
This is intended to help those who wants to do lift and shift of SSIS packages to Azure data factory and have got file system tasks in the control flow of their packages for copying files between two directories. Examples for such tasks could be loading a fresh copy of a file from a template file and loading some data into it from Database etc. I would write a seperate blog content on loading data from Azure DBaaS (Managed instance) to an excel sheet kept in a blob container.
I am using Visual Studio 2017 professional edition with SSDT tools for SSIS.
Also download the Azure feature pack for SSIS and install it from below.
https://docs.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis?view=sql-server-2017
Once you are done installing it, please restart your visual studio 2017 to make it appear in tools and other places in the project.
Steps:
Right click on the connection manager pane, and select new connection-->Azure storage. Fill in the storage account name and access key from Azure portal.
I am adding a snapshot from my storage account in azure portal for the beginners to navigate easily in azure portal.You can take either key1 or Key2 below.
(It supports both access key and managed entities for authentication.)
Supply the key (only the access key , not the whole connection string) and the account name and choose Azure default in environment .
We would get to see an entry added like below among connection manager entries.
We also would get to see tools under a new category named Azure in SSIS Toolbox
Testing a file copy task with Flexible file task dragged from the azure list in SSIS toolbox.
I have edited the task with source as an excel sheet named Ssis Test.xlsx in ssistestsource blob container and the target as ssisdest (blob container) and ssis text.xlsx.
I dont need to enter the whole connection string of the storage account as we already have it in our connection manager entry above.
This is supposed to create a destination container if it is not there, you should provide the name in lower case as storage container name like a few other azure resources, cannot be of upper case.
Executing the task by selecting it and clicking on the green button (Or right click on the task and choose execute).
The task went fine and file got copied over to the destination container.
That brings us to the end of this blog . Please access my homepage for reading the other content and comment below for anything you want me to write on or a feedback/value addition to the existing blogs.
