SSIS Expression Task

In this section, we'll learn how to create an expression task and how to use expression builder to create an expression.

Following the steps below to get a copy of an existing package.

  1. Open the project LearnSSIS1 which was created in previous sections in Visual Studio 2012.

  2. Right click the package Variables.dtsx in Solution Explorer and choose "Copy".
  3. Copy an Existing Package

  4. Right click "SSIS Packages" in Solution Explorer and choose "Paste".
  5. Paste the Package

  6. A new package Variables 1.dtsx was created and rename it to Expression.dtsx.

In previous section, the variables V1 and V2 are printed out but V3 cannot because V1 and V2 were changed in the script task and this makes V3 not be accessed until the task is done. To solve this problem, we'll add Expression Task to change V1 and V2 separately before the script task is run.

  1. Drag the Expression Task from SSIS toolbox and drop it above the Script task in the Expression.dtsx package.
  2. Add an Expression Task

  3. Select the Expression task and change the Name to "Change V1" in the properties window.

  4. Right click the Expression task and choose "Edit..." to open Expression Builder window as follows.
  5. SSIS Expression Builder Window

  6. In Expression Builder window, you can drag and drop the variables, functions and Operators to the Expression editing area to create an expression you want. In this example, we'll drag and drop User::V1 to editing area and input " = abc" then click "Evaluate Expression" button, you will get the error message below.
  7. SSIS Evaluate Expression Error Message

  8. Change "abc" to "2" and click "Evaluate Expression" button again you will get Evaluated value 3. At last, click "OK" button to save the expression you just created.
  9. SSIS Evaluate Expression Result

  10. Repeat the step 1-5 to create another Expression task and assign 5 to the variable V2. Alternatively, you can select the expression task, copy and paste to create another one then change it. After done, the whole package looks like below.
  11. SSIS Two Expression Tasks

  12. Select "Change V1" task, a green arrow will be shown up and drag the arrow and drop to "Change V2" task. Then select "Change V2" task and drag and drop the green arrow to the script task "Output Variables".
  13. SSIS add green arrows between tasks

  14. Edit Script task to remove all the "ReadWriteVariables" and add the variable V1, V2 and V3 to "ReadOnlyVariables". Then click "Edit Script..." but to change the code as follows.
  15. Change Script Task C# Code

    The line 101, 102 were commented out and the 2 forward slashes in line 105 were removed to print the variable V3.

  16. Run the package to get the result.
  17. SSIS Output Variable V3 Running Result

    At last, click "OK" button and click "Stop Debugging" or Shift F5 to terminate running of the package.