How to Looping through Result Sets in SSIS For Foreach Loop Container

Problem: When you would like to loop through result sets stored in an object variable, you probably will use Foreach Loop Container in SSIS. If you need to read multiple fields from object variables to use in the down stream later, how to implement it?

Solution: 

To specify the mapping by indexes in "Variable Mapping" of Foreach Loop (FEL) container.

Here we use a dynamic parent-child loading pattern as an example. For details about dynamic parent-child loading pattern, please refer to the book "SQL Server 2012 Integration Services Design Patterns".  Basically, this design pattern uses a control table to control which tables to load and which child packages to call at this execution. Later in the Foreach Loop, we can use Execute Package task to call related child packages. This dynamic parent-child design pattern is especially helpful when you have lots of tables to refresh while refreshing frequencies are different. So the parent package will be designed as shown below. It consists of two main tasks: one Execute SQL task to extract child packages to call for current execution; the other is Foreach Loop container to loop through result sets from above Execute SQL task, also use Execute Package Task to call child package at each loop.

We extract two columns from a control table by SQL scripts and loaded the results into an Object variable called User::PkgList. 

SELECT TableName, [ChildPackageName]
FROM [dbo].[PackageListWithOrder]
where IsLoad = 1
order by LoadOrder 

As shown below, User::PkgList contains table names and related package names.

Step 1: Define the ADO enumerator as usual in Foreach Loop Container.

Step 2: Go to "Variable Mappings", then specify the variables to be mapped with,following the order in your Object variables with index starts from 0. Note that you can't find the "Add" button to add additional mapping. It doesn't matter. You just click the space after first row, then you can specify the new mapping.

Then you can use these two variables in Execute Package Task to call and pass through related child packages. Run the package to see the variables' values got updated when looping through Foreach loop! Let me know if you have any questions.

How to Upload Data Files to Azure Blob Storage?

Problem: You have text files in csv or AVRO formats and you would like to upload them to Blob storage as intermediate steps of big data processing in Azure. How to upload them to Azure Blob storage?

Solution: 

Use "Azure Blob Upload Task" or  "Azure Blob Destination" in Data Flow Task in Integration Service in SQL Server (SSIS).

Before we open new Integration Service project in Visual Studio 2015, you need to do preparations to get our environments setup:

1. Sign up Azure account.
2. Install Azure feature pack for SQL Server and ensure that Azure tools appear in Visual Studio. If Azure tools are grey out, please refer to the article to resolve issues.
3. Install Microsoft Azure Storage Explorer and create a storage account.

     After you create a storage account, you should see four types of storage appearing under your storage account at Azure Storage Explorer:

        Then you can create a Blob container to contain the file to be uploaded. You can easily do it by using Microsoft Azure Storage Explore shown below. Here the Blob Container called "ssisdemoblob" is created and under this a Blob Directory "data" is also created. The summary of naming rules for Azure can be referred as this article.

Now you can go ahead to open visual studio and create a new SSIS project. 

Step 1: Create Azure Storage Connection.

            - Choose "AzureStorage" as Connection manager type. 

            - For Account Key in "Azure Storage Connection" editor, copy "Access Key" in your Azure account. 

            - Click "Test Connection" to make sure it connects successfully to your Azure account.

Below shows how to get access key for your storage account from Azure Portal.

Note that you can create Azure Storage Connection under project level connections as shown below so every package under this project can share the same Azure Storage connection.

Step 2: Configure the Azure Blob Upload Task in SSIS as shown below:

To make this upload more dynamic, here we use variable @[User::srcFolder] for local directory. You can use expression for other properties such as BlobContainer, BlobDirectory, Disable.

Moreover, you can use wildcard for FileName as in other SSIS solutions.

After executing this task, you can see the related file appears under the Blob Directory specified. Now you have your first file uploaded to Azure Blob Storage!

Note that you can also use "Azure Blob Destination" in Data Flow Task to achieve this.
Azure Blob Upload Task can be easily used to upload one more files to azure Blob storage by using wildcards for FileName. While for "Azure Blob Destination" in Data Flow Task, you have to use Foreach Loop Container together with Azure Blob Destination to load multiple files to Blob Storage.

Why "Azure" related tools are grey out in SSIS after installation of Azure feature pack?

Problem: After installation of Azure feature pack for SSIS, Azure related toolboxes are still grey out?

Solution: 

If Azure related toolboxes are grey out in SSIS, that means that the version of SQL server is different from the version of Azure feature pack. You need to change the TargetServerVersion in visual studio to the same version of Azure feature pack you installed.

For example, if you download and install Microsoft SQL Server 2016 Integration Services Feature Pack for Azure for SQL Server 2016, you need to set TargetServerVersion in visual studio to be SQL Server 2016 as shown below:

Now your project version will show: SQL Server 2016 and Azure related tools will show up in your SSIS toolbox!