Thursday, 11 June 2015

Azure Blob Enumerator

Microsoft released the SSIS Feature Pack for Microsoft Azure (20122014), but how does the Foreach Azure Blob Enumerator work?
Foreach Azure Blob Enumerator

The Azure Blob Enumerator works just like any other enumerator. The best combination is with a Data Flow Task and an Azure Blob Source. It returns the blob filename on Azure including the blobpath: MyFolder\Weather.csv. Perfect for the Azure Blob Source where it is also one value.

1) Storage Account
First make sure you have an Azure account that has an storage account in it. Besides the name the location is also important. Especially for the Azure HDInsight Create Cluster Task. Mine is called SSISJoost.
Storage Account

2) Access Keys
Click on the Manage Access Keys icon to get the primary access key. We need this for the Azure Storage Connection Manager.
Storage Account Access Keys

3) Connection Manager for Azure Storage
If you haven't already installed the SSIS Feature Pack for Microsoft Azure then now it's time to do that. Create a new Connection Manager for Azure Storage by right clicking the Connection Managers Pane. Then choose New Connection... and then AzureStorage. Fill in the Storage account name from step 1 and the Access key from step 2. Test the connection and click OK to save the new Connection Manager.
SSIS Connection Manager for Azure Storage

4) Variable
Add a string variable to store the azure filepath in. Mine is called AzureBlobPath
String variable

5) Foreach Azure Blob Enumerator
Add the Foreach Azure Blob Enumerator to the surface of your control flow and edit it. On the collection page choose Foreach Azure Blob Enumerator.
Azure storage connection: use the connection manager from step 3 or create a new one.
Blob container name: specify the containername of Azure where the files are stored.
Blob path: The optional 'subfolder'. Add / if you didn't use 'subfolders'.
Blob file name filter: specify the wildcard to select the files.
Blob file modified after and Blob file modified before: Optional date filters.
Foreach Azure Blob Enumerator

Blob container name, Blob path & Blob file name filter

5) Expression
Now go to the properties of the data flow task that contains the Azure Blob Source Component. Just like the XML source the expressions are not on the source component itself. Now you can add an expression on the Blob Name to replace it with the value of the string variable @[User::AzureBlobPath] from step 4.
Expressions on Data Flow Task properties

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...