SSIS Script Task

In this section, we'll introduce the first task - Script Task. Script Task is actually the most powerful task when developing an SSIS package. It is always the last choice if no built-in tasks can be used to realize the functions we need. The Script task uses VSTA (Microsoft Visual Studio Tools for Applications) as the developing and running environment. So VSTA must be installed on the machine where the package runs.

Let's learn the task by customizing the package Variables.dtsx which we created in previous section.

  1. Open the package Variables.dtsx in design mode. Drag and drop the Script Task from SSIS Toolbox to the package. Then right click the Script Task and choose "Edit...".
  2. SSIS Script Task Edit


  3. The Script Task Editor will be opened as below.
  4. Script Task Editor Script Tab

    • ScriptLanguage: You can choose VB or C# as the script language.
    • EntryPoint: This is the start running method in the script and by default it is Main.
    • ReadOnlyVariables: Specify the read only variables which will be used in the script.
    • ReadWriteVariables: Specify the read/write variables which will be used in the script.

  5. Choose "Microsoft Visual C# 2012" as our script language and click ... button at the end of ReadOnlyVariables to open "Select Variables" window as below.
  6. SSIS Select Variables

    We'll select the following system variables and click OK button.

    • System::TaskID
    • System::TaskName
    • System::PackageName
    • System::StartTime

    Then click ... button at the end of ReadWriteVariables to open "Select Variables" window and choose User::V1, User::V2 and User::V3 variables we defined in the previous section. Then click OK.

    Pay attention, the System and User are namespaces.


  7. Click General tab and change the task name and description as below.
  8. Change Script Task Name and Description


  9. If you click Expressions, you can replace any property with the result of the expression at run time. We'll tell you more in the next section. After the above settings, the Editor looks like below.
  10. Script Task Editor After Setting Variables


  11. Click "Edit Script..." to open VSTA and add the following code and click OK to save the settings.
  12. Script Task code to output variables


  13. Run the package by either of the following 4 options.
    • Click the menu DEBUG -> Start Debugging.
    • Press F5 button.
    • Press Start icon under the menu.
    • Right click the package in solution explorer and choose "Execute Package".

    The following are the screen shot of the result.

    Script Task Running Result


Explanation in the code:

  • The value of a variable can be invoked as follows in C# code.
  • Dts.Variable[""].Value
    
  • Line 95-98: Prepare for printing system variables.
  • Line 101-102: The values of the 2 user-defined variables are changed.
  • Line 105: This line is commented out because the variable V3 was defined by an expression and the variable V1 and V2 are changed in line 101-102. It makes the V3 unstable and cannot be printed.

So how to output the variable V3, please check the next section.