How to set SSIS Data Flow component property using expression and variable

Introduction

SSIS PowerPack supports various methods for making things dynamic. Sometimes you have to consume JSON Data from a Dynamic URL. There are 3 different ways you can make JSON Source URL Dynamic.

  • Use Expression on the DirectPath property of the JSON Source (SSIS Data Flow Expression)
  • Use variable placeholders directly inside URL (Only works when you use DirectPath mode)
  • Use PathFromvariable AccessMode and define an expression on an SSIS variable

Use SSIS Data Flow Expression

So you change any property of the SSIS Data Flow component at runtime using an expression:

  1. To define an expression for any property of a component, first navigate to the Data Flow designer surface.SSIS DataFlow Expression - Change Component Property at runtime
  2. Now, right-click anywhere in the data flow designer surface and click the "Properties" menu item.
  3. When the Properties window appears, select a property and press the [...] button to set an expression.
  4. Now enter the property and expression you need.SSIS DataFlow Expression - Edit Component Property Expression

Use Variable Placeholders

SSIS PowerPack supports Variable Placeholders for easy editing and making values dynamic. Variable placeholders are directly visible, unlike expressions, which are hidden. See the screenshot below for an example of how to type a variable placeholder, which gets replaced by the actual value of the SSIS variable at runtime.

SSIS dataflow Expression alternative – Using Variable Placeholders

PathFromVariable mode and Use Expression on Variable

If you choose a path from a variable, then you can define an Expression on the variable to make your path dynamic at runtime.

Making Task Property Dynamic using SSIS Expression

Check this article if you want to make the Task Property dynamic

 

 

 

Posted in SSIS Components and tagged , , .