SSIS - How to Write Expressions on Data Flow Task for Derived Column Transformation Expressions for derived column

Scenario:

We are developing an SSIS Package and writing business logic by using Derived Column Transformation in Data Flow Transformation.
This business logic is subjected to change. We don't want to open the package every time the business logic changes, Instead of that we want to use configuration to handle the situation. If the logic changes, we will update the value in configuration table or file and package should read the value and apply in Derived Column Transformation.

Solution:

Let’s say we are loading data from flat file to SQL Server table, and flat file has two columns ID and Name.  We need to derive initials from Name column and business logic say that always take the first character from Name column as Initials.

Step1:
You went ahead and used the Flat file source to read the data, then used the Derived Column Transformation and wrote expressions to get the first Characters as shown below.

How to write expression on Data Flow Task to Change business Logic in Derived Column Transformation in SSIS Package

Step2:

Now consider that business decieded to change the logic for Intials. They want you to take two characters from Name column as Initials instead of first single character. That can be easy, you will open the SSIS Package and then make change in Derived Column expressions for DER_Name (Initials Column), save the package, test in QA,UAT and finally deploy to production.

This is long process. You want to take different approach , where you don't have to make any changes to SSIS Package, rather handle the logic change by using variable in SSIS Package and change the value of the variable in Configuration table or file.

To implement this, let's create a variable called VarBusinessLogic as shown below

Create Variable in SSIS Package and use in Data Flow Task to Change Expressions in Derived Column Transformation

Step 3:
As our variable is ready now and it has the value that we would like to use in Derived Column Expressions, Go to Properties of Data Flow Task, click on expressions and use VarBusinessLogic variable as shown below.

How to change expressions in Derived Column Transformation by using Variable value in SSIS Package

By using friendly expression on Data flow task we were able to write expression(Business logic) in derived column.

Step 4:
The final step is to add the VarBusinessLogic variable in Package Configuration. Now if the logic changes, you don't have to make any change to SSIS Package, instead of that you will update the value for VarBusinessLogic variable in Configuration table or file and Package will read and implement in Derived Column Expressions.

This can be helpful when business logic often change and you don't want to make change to package every time for small change.