2

Currently, I have a file path value hard coded inside the Script task of an SSIS package.

I have a string variable sPath. How should I use this variable sPath inside Script Task?

string strPath = "C:\\File.xls";
if( File.Exists(strPath))
{
    File.Delete(strPath);
}
goofyui
  • 3,362
  • 20
  • 72
  • 128

1 Answers1

7

Here is one possible way of using variables inside Script Task. Assuming that you have a variable named FilePath declared on your package as shown in screenshot #1 then you can use the following code to use the variable inside the Script Task. this is one of the possible ways to use the variable. Here the variable is used only to read the value using the method LockForRead. You can also write values to the variable if the variable is declared with LockForWrite method.

By the way, the functionality described in the Scrip Task code can also be carried out using the File System Task available in SSIS Control Flow task list.

Hope that helps.

Using package variables inside Script Task:

C# code that can be used only in SSIS 2008 and above. .

public void Main()
{
    Variables varCollection = null;
    string FilePath = string.Empty;

    Dts.VariableDispenser.LockForRead("User::FilePath");
    Dts.VariableDispenser.GetVariables(ref varCollection);

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

    if (File.Exists(FilePath))
    {
        File.Delete(FilePath);
    }

    varCollection.Unlock();

    Dts.TaskResult = (int)ScriptResults.Success;
}

Screenshot #1:

1

  • Thats the perfect answer !! Thank you – goofyui Jun 06 '11 at 18:29
  • Don't know why this hasn't been accepted. Very helpful, thanks. – cutts Apr 05 '12 at 10:10
  • Which namespaces should we including in our `using` statements for the compiler to resolve the `Variables` declaration? I had to replace `Variables` with `IDTSVariables100` and also replace the `ref` modifier with `out` when calling the `GetVariables` method. – Zarepheth Jul 30 '12 at 18:00