Dynamic Properties / Parameterization for Connection

Content

Introduction - How to make things dynamic in SSIS
Parameterize Connection Properties
- Method #1 - Parameter Binding
- Method #2 - SSIS Expression
Parameterize Task / Component Properties
- Method #1 - SSIS Expression
- Method #2 - Placeholder Functions (Only for ZappySys Tasks / Components)

Introduction - How to make things dynamic in SSIS

In this help doc you will learn various techniques to make one or more properties of your Connection / Task / Component dynamic in SSIS. We will show multiple approaches so you can choose based on your need.

Parameterize Connection Properties

In this section you will learn how to parameterize property of your connection (e.g. ServerName, Password, UserName etc) using different ways.

Method #1 - Parameter Binding

You can bind any connection property to Package/Project Level Parameter by using simple right click menu (Same can be done from Expressions but more clicks). You can pass Parameter Value from Job / Command line and it updates Connection Propery if you parameterized this way. If you need advanced logic than simple value binding then check SSIS Expression approach in next section.

In the SSIS Connection Manager Pane, Right click on the connection name which you like to parametrize and click Parameterize option from the menu.
Now choose Property you like to make dynamic from the dropdown (e.g. Password Property)

Method #2 - SSIS Expression

Now let's look at more advanced usecase to make connection property dynamic using SSIS Expression. This way you can use Expression Engine functions, Variables, Parameters unlike simple Binding listed in the previous approach.

In the SSIS Connection Manager Pane, Right click on the connection name which you like to parametrize and click Properties option from the menu.
Now from Properties Grid find Expressions its usually under Misc. Click on Button to open Expression Editor
Now you can choose property you like to make dynamic from the dropdown and then click Editor Button to open Expression Editor like below. Here you can Drag and Drop Variable / Parametger names and make your Property value dynamic as per your need. You can also use functions supplied in Expression engine (i.e. TRIM, UPPER).
```
Notice that if you use double quotes in your value you have escape using slash before it like below screenshot
```

Parameterize Task / Component Properties

In this section you will learn how to parameterize property of your Tasks / Components using different ways.

Method #1 - SSIS Expression

You can make Task Properies dynamic same way we saw using SSIS Expression approach for connection (in the previous section). For more information check this article.

In the SSIS Connection Manager Pane, Right click on the connection name which you like to parametrize and click Properties option from the menu.
Right click on the Task and Choose Properties (if its data flow Right click in Designer)
Now from Properties Grid find Expressions its usually under Misc. Click on Button to open Expression Editor
Now you can choose property you like to make dynamic from the dropdown and then click Editor Button to open Expression Editor like below. Here you can Drag and Drop Variable / Parametger names and make your Property value dynamic as per your need. You can also use functions supplied in Expression engine (i.e. TRIM, UPPER).
```
Notice that if you use double quotes in your value you have escape using slash before it like below screenshot
```

Method #2 - Placeholder Functions (Only for ZappySys Tasks / Components)

Most of ZappySys Tasks and Data Flow Components support simpler version of SSIS Expression engine known as ZappySys Placeholder Engine. For more information check this help page. Advantage of placeholder compare to SSIS Expression is that enhanced function library and ease of use.
Here are some examples how you can insert Placeholders anywhere in the property value to make things dynamic.

{{User::SomeVariable}}
{{$Project::ApiToken}}
{{$Package::SellerId}}
{{$Package::SellerId,FUN_LOWER}}
{{System::StartTime,yyyy-MM-dd-HH-MM}}
<<FUN_TODAY>>
<<FUN_TIMESTAMP_UNIX>>
<<2010-01-01T00:00:00,FUN_TIMESTAMP_UNIX>>
<<{{System::StartTime}}{{System::TaskName}},FUN_BASE64ENC>>

Here is how to use Placeholders in certain properties. You cannot use this approach in all properties so check UI see it has option to insert placeholder or check documentation see if Property supports placeholder.

Open Task / Component UI.
Find out edit icon / variable icon next to Textbox / Grid. For example below property supports Placeholder to make Path / URL dynamic.
You can Preview Value if its supported like below