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

Introduction

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

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

Use SSIS Data Flow Expression

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

  1. To define an expression on any property of a component firstly go to data flow designer surface. SSIS DataFlow Expression - Change Component Property at runtime
  2. Now right click anywhere in data flow designer surface and click "Properties" menu item.
  3. When Properties window appears select a property and press [...] button to set an expression. SSIS DataFlow Expression - Edit Component Property Expression

Use Variable Placeholders

SSIS PowerPack supports Variable Placeholders for easy editing and making value dynamic. Variable placeholders are directly visible unlike expressions which are hidden. See below screenshot how you can type variable placeholder which gets replaced by actual value of SSIS variable at runtime.

SSIS dataflow Expression alternative - Using Variable Placeholders

SSIS Data Flow Expression alternative – Using Variable Placeholders

PathFromVariable mode and Use Expression on Variable

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

Posted in SSIS Components and tagged , , .