How to Access Ssis Package Variables Inside Script Component

How to access ssis package variables inside script component

Accessing package variables in a Script Component (of a Data Flow Task) is not the same as accessing package variables in a Script Task. For a Script Component, you first need to open the Script Transformation Editor (right-click on the component and select "Edit..."). In the Custom Properties section of the Script tab, you can enter (or select) the properties you want to make available to the script, either on a read-only or read-write basis:

Then, within the script itself, the variables will be available as strongly-typed properties of the Variables object:

// Modify as necessary
public override void PreExecute()
{
    base.PreExecute();
    string thePath = Variables.FilePath;
    // Do something ...
}

public override void PostExecute()
{
    base.PostExecute();
    string theNewValue = "";
    // Do something to figure out the new value...
    Variables.FilePath = theNewValue;
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    string thePath = Variables.FilePath;
    // Do whatever needs doing here ...
}

One important caveat: if you need to write to a package variable, you can only do so in the PostExecute() method.

Regarding the code snippet:

IDTSVariables100 varCollection = null;
this.VariableDispenser.LockForRead("User::FilePath");
string XlsFile;

XlsFile = varCollection["User::FilePath"].Value.ToString();

varCollection is initialized to null and never set to a valid value. Thus, any attempt to dereference it will fail.

how to get a variable's value from Script Task in ssis

From what I can tell, you are retrieving the EmployerId from the file name, correct? If yes, you don't need to use a script task. Just create a new variable and set an expression to parse the EmployerId. If you can provide the file name, I can help with the expression.

Update

Based on the variable character length and the inability to change the file name to include another delimiter, you will need to handle this in a script task by using regex. See the weblink below:

https://social.technet.microsoft.com/wiki/contents/articles/22194.use-ssis-variables-and-parameters-in-a-script-task.aspx

In the control flow:

1. Create a filename variable of type string that is set by the for each loop enumerator
2. Create a EmployerID variable of type int32

In the script task:

3. Select the filename as read-only and EmployerID as read-write
4. Get the filename value
5. Use your regex to get the EmployeID
6. Set the EmployerID variable

How can I access a variable across script tasks within a package?

When you create variables in SSIS packages, you can define the scope of the variable. This scope defines which tasks on the Control Flow have visibility to the variables.

Sample package describing variable scopes:

Here is a sample SSIS package.

I have created a package with a sequence container and two Script Tasks within it. I have created four variables under different scope.

If you want to view all the variables defined under different scopes, you need to check the fourth button on the Variables pane. The option is indicated by the arrow in the screenshot.

Here is how the variables work in each of these scope:

StartTime_Package - This variable is declared under the scope MyPackage, which also happens to be the package name. This is the top level scope. This variable will be visible to all the tasks on the Control Flow.

StartTime_Sequence - This variable is declared under the scope Sequence Container, which is the given name of the sequence container task. This variable will be visible only to the Sequence container and the tasks within the sequence container.

StartTime_Task1 - This variable is declared under the scope Script Task 1, which is the given name of the first script task within the sequence container. This variable will be visible only to the first script task and no other tasks.

StartTime_Task2 - This variable is declared under the scope Script Task 2, which is the given name of the second script task within the sequence container. This variable will be visible only to the second script task and no other tasks.

How can I check if a task can access a variable or not?

Here is an easier way to identify if a particular task can access a variable or not. Let's uncheck the third option on the Variables pane.

Click on the Script Task 1. You will notice that the variable StartTime_Task2 is not displayed on the Variables pane because Script Task 1 does not have visibility to it.

Likewise, you can click on the task and verify which variables it can access.

You have to decide what the scope of the variable should be based on your requirements. If you are going to share the value of a variable across tasks, it is safe to declare it at the topmost package level scope.

If you are very sure that you will not access a certain variable outside of a particular task, it is safe to declare it at that task's scope level.

How to read a variable or write a value to variable using Script Task?

Double-click on the Script task, it will bring the Script Task Editor. You have to determine whether you just want to read the variable values or modify it within the task. I am going to modify a variable value and then display the value in a message box. To do that, I have to pick a variable that is already declared on the package and provide the script task the read and write access. Click the Ellipsis button against the ReadWriteVariables property

Select Variables will list the variables (both system and user level) to which the script task has access to. I am going to pick StartTime_Package variable.

You can see the variable now listed in the property. You can select multiple variables to. Click Edit Script so we can modify the C# code to write a value to the variable and then read it.

Paste the following code into the Script Task. First line assigns the value, here I am just adding 7 days to today's date. Second line displays the value of the variable in a message box.

public void Main()
{
    Dts.Variables["StartTime_Package"].Value = DateTime.Now.AddDays(7);

    MessageBox.Show(Dts.Variables["StartTime_Package"].Value.ToString());
    Dts.TaskResult = (int)ScriptResults.Success;
}

If we run the package, it will display the value in message box. The package was executed on November 1, 2012 and you can that the package is displaying the modified value of November 8, 2012.

Hope that gives you an idea about variables scope within SSIS.

How to Get Sensitive Information Into SSIS Script Component in a Data Flow

Despite the lack of a GetSensitiveValue method existing in the Script Component, I was able to access the value just fine.

What I did fumble with was my Package Protection level and how it interacts with Project Parameters that are marked as Sensitive.

I defined a Project Parameter named MySecretPassword and populated it with SO_71308161 and marked it as sensitive.

Script Source

I defined a single column output and my intention was to just push the password into the dataflow to confirm I was able to access it

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public string MySecret;
    public override void PreExecute()
    {
        base.PreExecute();
        MySecret = Variables.MySecretPassword;
    }

    public override void CreateNewOutputRows()
    {
        Output0Buffer.AddRow();
        Output0Buffer.Column = this.MySecret;
    }

}

I then routed the data to a derived column and dropped a data viewer between the two. My code did not throw an exception but it did show an empty string.

If I tried to access my sensitive parameter value in a script task using myVariable.Value it would throw an error as expected but I was getting my sensitive value back.

Package Protection Level

I love StackOverflow questions that teach me things. My default mode is to define projects that use a Project & Package protection level of DontSaveSensitive. Which is incompatible with using Sensitive project parameters. Not incompatible in that it will throw an exception, but when you run the package, accessing that value will be blanked out (for strings at least).

As soon as I change the Project and then Package's protection level to EncryptSensitiveWithUserKey (or anything that isn't DontSaveSensitive), both the Script Task's .GetSensitiveValue() and the Script Component's Variables.MySecretPassword worked.

---

Related Topics