Ashfaq Ahmed Ashfaq Ahmed - 11 days ago 8
SQL Question

How to access ssis package variables inside script component

i am new in SSIS, working on a project, and trying to access SSIS package variables inside my c# code which i have used in
Data Flow -> Script Component - > My c# Script

can anyone help on this ?

also i have tried with which is also not working

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

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

Answer

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: screenshot of Script Transformation Editor properties page 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.