Browsed by
Tag: Azure Feature Pack for SSIS

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