in Development

SSIS Connection Manager Issue with Azure Storage Account Key

On a recent project, I was attempting to use Microsoft SQL Server Integration Services (SSIS) to read files from Azure Blob Storage using the Azure Feature Pack for Integration Services.  Using the Azure tasks seemed pretty straight forward until I couldn’t get them to work in the Control Flow for my DTS package.

The error message left me a bit baffled:

Connection manager “myAzureBlobConnectionManager” (SSIS Connection Manager for Azure Storage): Azure Storage Connection Manager is invalid due to missing required property: AccountKey

I was confused because I was sure I set the Azure Account Key when I created the Azure Storage Connection in the Azure Storage Connection Manager Editor as shown below.  I even clicked the “Test Connection” button to make sure it worked.

After a lot of searching, I found-out that the Connection Manager Editor does not retain the Access Key after you insert it.  Apparently it only use it to test the connection.

In order to make the Access Key “stick”, or rather be read-in from somewhere, I opened the Expressions section of Connection Manager’s properties and created an Expression that assigns the Access Key to a parameter or a variable.

In order to do that, first select the Azure Connection from the Connection Managers window:

Then add a new Expression in the Properties window for the Azure Connection Manager:
The Property Expressions Editor window will appear when attempting to add a new Expression.  In the Property column, select “AccountKey” from the dropdown list that appears.  In the Expression column, select the parameter or variable you wish to use to store the Azure Access Key.

After clicking OK, you’ll see the new expression was added:

After that simple but highly unintuitive change, the Azure Blob Download Task should work.

Write a Comment

Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.