Browsed by
Tag: Azure

Dynamic Parameterization of Azure SSIS Data Flow Components

Dynamic Parameterization of Azure SSIS Data Flow Components

In this article I would like to explain how to dynamically parameterize some of the Azure Data Flow Components settings.

Introduction

With Azure Feature Pack for SSIS, 4 new Data Flow Components were added:

  • For Blob
    • Azure Blob Source
    • Azure Blob Destination
  • For Data Lake
    • Azure Data Lake Store Source
    • Azure Data Lake Store Destination

 

Data Flow Components shipped with Azure Feature Pack for SSIS
Data Flow Components shipped with Azure Feature Pack for SSIS

Very often there is a need to parametrize some of the settings of these components.
One of the most useful parameters can be:

  • For Blob Components (Azure Blob Source & Azure Blob Destination)
    • Blob container name
    • Blob name
  • For Data Lake Components (Azure Data Lake Source & Azure Data Lake Destination)
    • File Path

On below screenshots Azure Blob Destination Editor (left hand side) and Azure Data Lake Store Destination Editor (right hand side) windows are shown as an example (Azure Blob Source Editor and Azure Data Lake Store Source Editor look similar). What we can easily see is that on:

  • Azure Blob Destination Editor
    • Blob container name
    • Blob name
  • Azure Data Lake Store Destination Editor
    • File Path

are just a text boxes without any chance of dynamic manipulation of the values.

 

Azure Blob Destination Editor
Azure Blob Destination Editor

Azure Data Lake Store Editor
Azure Data Lake Store Editor

 

When we check the Properties of these 4 components (RMB click on the component, then select Properties), we indeed see that these settings are visible, but still there is no mechanism to parametrize them (Below example for Azure Blob Destination Editor (left hand side) and Azure Data Lake Store Destination Editor (right hand side)).

 

Azure Blob Destination Properties
Azure Blob Destination Properties

Azure Data Lake Store Destination Properties
Azure Data Lake Store Destination Properties

 

The Solution

The solution is:

You are allowed to parametrize these settings on a whole Data Flow component level

In order to do this, instead of editing Properties of these 4 Components itself, edit the Properties of the parent Data Flow Component that encapsulates these 4 Components (RMB click on the whole Data Flow component, then select Properties).

When you do this, you see Expression Property Item. You can click for […] button (again, examples only for: Azure Blob Destination Editor (left hand side) and Azure Data Lake Store Destination Editor (right hand side)):

 

Properties on Data Flow Task level for Azure Blob Destination
Properties on Data Flow Task level for Azure Blob Destination

Properties on Data Flow Task level for Azure Data Lake Store Destination
Properties on Data Flow Task level for Azure Data Lake Store Destination

 

Then you can assign dynamic values based on your variables and/or parameters.

List of Properties that can be parameterized for these 4 Components are:

  • For Blob
    • Azure Blob Source
      • Blob Container
      • Blob Name
    • Azure Blob Destination
      • Blob Container
      • Blob Name
  • For Data Lake
    • Azure Data Lake Store Source
      • FilePath
      • FirstRowAsColumnNames
    • Azure Data Lake Store Destination
      • BathSize
      • ColumnDelimiter
      • FileFormat
      • FilePath
      • FirstRowAsColumnNames
      • IsBatchMode

 

Below just a visual representation of all Properties for these 4 Azure Data Flow Components:

 

Azure Blob Source Component properties
Azure Blob Source Component properties

Azure Blob Destination Component properties
Azure Blob Destination Component properties

Azure Data Lake Store Source Component properties
Azure Data Lake Store Source Component properties

Azure Data Lake Store Destination Component properties
Azure Data Lake Store Destination Component properties

 

Azure CLI 2.0 (preview) at first glance

Azure CLI 2.0 (preview) at first glance

Around four months ago Microsoft announced a new version of its Azure Command-Line Interface with the name Azure Command-Line Interface 2.0
If you want to read more, use this URL: https://azure.microsoft.com/en-gb/blog/announcing-azure-cli-2-preview 

Three weeks ago a newest beta of this CLI was shared to be publicly available:
https://docs.microsoft.com/en-us/cli/azure/install-az-cli2
There are versions for three major OS: Windows, Linux and OS X. There is also Docker image.

Microsoft decided to completely redesign it’s CLI backbone. Azure CLI is based on NodeJS whereas Azure CLI 2.0 is based on Python. Azure CLI 2.0 is still in Preview version, and I haven’t found any exact date when it will be officially release in non-preview state.

I decided to install Azure CLI 2.0 on Linux, in order to check whether the process is as really simple as Microsoft states on a it’s website. I used Linux Mint (newest version 18.1), mostly because I really like this distro.

Installation

Here is the full article how to install Azure CLI 2.0: https://docs.microsoft.com/en-us/cli/azure/install-az-cli2

I used instructions from the section connected with the newest version of Ubuntu (Ubuntu 16.04 LTS), as Linux Mint
is based on Ubuntu (which in turns Ubuntu is based on Debian). The instruction itself is not very sophisticated and consists of just two steps.

First step is about installation and update some packages, like SSL dev libs , Foreign Function Interface libs,
python dev and build-essential:

sudo apt-get update && sudo apt-get install -y libssl-dev libffi-dev python-dev build-essential

Microsoft encourages that If you haven’t installed any Python yet, install version 3.5.X at least, but installation went smoothly on my 2.7.12 version of Python:

Azure CLI 2.0 works also well with Python 2.7.X

 

Second step (and the last one) is about downloading and installation a new Azure CLI 2.0.
Installer will be run automatically and will ask some simple questions about localisation where the new CLI
will be installed.

curl -L https://aka.ms/InstallAzureCli | bash

Again, this step also went really smoothly.

After this, we can just start playing with new Azure CLI 2.0 by typing az in terminal.

Yes, there is a change:
the current version (CLI 1.0) uses azure as a main command whereas the upcoming major release (CLI 2.0)
uses az command instead of azure

Azure CLI 2.0 interface after installation