How to use Azure Storage Emulator for Testing

Problem: During development of SSIS packages for data transferring between Azure Storage, you want it to be fully tested on your local first before any data transfers to your Azure account. You also want to avoid unnecessary charges on your Azure account for testing. How to achieve this?

Solution: Using Azure storage emulator

We know that Azure storage is charged based on volume of data stored every month, types of operations performed, and amount of data transfers involved base on Azure Storage pricing.

In order to avoid unnecessary charges during development and testing, we can leverage Azure storage emulator to test and debug Azure cloud services locally before real data transfer occurs between on-premise and Azure account.

First, you can download and install Azure storage emulator here. During installation, you need to take note of installation paths of Azure emulators. Here mine is installed by default at: 

C:\Program Files (x86)\Microsoft SDKs\Azure\Storage Emulator

After installation, start the storage emulator by searching "Azure Emulator" after clicking windows Start button. 

If it is executed for the first time, it will do initialization first by creating a database in LocalDB and granting database access for current user.

You can also check the database created by Storage Emulator as shown below:

How to set connection to Azure storage emulator?

Here we use upload files to Azure Blob storage by SSIS as an example. (As for how to set up Azure environments for SSIS, please refer to the steps in "How to Upload Data Files to Azure Blob Storage". )

At connection manager pane in SSIS, choose "New Connection", then choose "AzureStorage" as shown below.

Next, choose "Use local developer account" instead of "Use Azure account". Then click "Test Connection" button to ensure connection with storage emulator is successful. Ensure that you have storage emulator running. You can check the status of emulator by running the following command on command console:

C:\Program Files (x86)\Microsoft SDKs\Azure\Storage Emulator>AzureStorageEmulator.exe status

You can rename the connection to the name you want. Here I rename it to "azure-emulator" for future use.

To use it with Azure related task, the main change is the connection to be the Azure emulator one you just created. Below shows the configuration for Azure Blob Upload Task. Now you are good to go to debug and test your package on your local!

Help Command in Storage Emulator

You can use help command to quickly locate commands for operations on storage emulator.

Note that storage emulator is an emulated environment running on a local SQL instance, there are Differences between the storage emulator and Azure Storage, in aspects of Blob Storage and table storage.

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.