SSIS: How can you enumerate through files in Azure Storage and load each file into SQL Server

user_6420744 0
Viewed 1025

I would like to do something that seems straightforward, but I am not having much joy. I want to build an SSIS Package using the For Each Loop container configured for the Foreach Azure Blob enumerator to loop through the files in my storage container. I can get the loop to indeed loop through all the files. The problem comes when I try to load each file using the 'Azure Blob Source' data flow component. It seems as if the 'Azure Blob Source' data flow component is not able to be provided with the blob name that should come from the Foreach Azure Blob enumerator. This results in the 'Azure Blob Source' data flow component loading the same file that is set as its 'Blob Name' over and over as the enumerator loops through all the files in the container. Surely it should be possible to provide a variable as the 'Blob Name' so that the package can call the Data Flow task for each file in the container.

azure enumerator ssis blob
1 Answers

I solved this by doing the following. Firstly you have to get the Blob Name from the Azure Blob enumerator stored in a package scoped variable. The foreach loop enumerator

I created a user variable called FilePath which is loaded with each blob name in the blob container as it loops through them. Within the foreach loop container I put a data flow task that has an 'Azure Blob Source' component in it. The Azure Blob Source component will load data from a specified text file. When you set up the component you specify the name an example text file in the format you are loading in order to define how it is imported. When the 'Azure Blob Source' component is located within the Azure Blob enumerator you would want it to be provided with the name of each blob in the container as it enumerates through them.

the data flow task within the enumerator

You do that by setting up an Expression at the Data Flow component level. You need to set up a conditional formula to provide a value for the 'Blob Name' even when the enumerator is not in operation or you will have and error saying there is no value for the Blob Name.

enter image description here

@[User::FilePath] == "" ? "example.dat" : @[User::FilePath]

The logic states that if the user variable FilePath has no value assigned, just use a fake filename to avoid the error about having no Blob Name in the Azure Blob Source, however if the enumerator is providing a name then use that as provided in User::FilePath

user_64207440
Related