none
Accessing Variables in SSIS code

    Question

  • Hi

    I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?

    Please let me know if theres a way to access SSIS variables.

    Thanks,

    Vipul

    Tuesday, November 21, 2006 6:48 AM

Answers

  • Custom data flow components, also known as pipeline components, inherit from the PipelineComponent class. This exposes a public read-only property called VariableDispenser, so you can access this in your own methiods, or those you override from the base class, PipelineComponent. This gives you access to the variable dispenser to read or write variables as you desire.
    Tuesday, November 21, 2006 8:37 AM
    Moderator
  • Hi Mike:

    I was able to solve it. Code correction:

    IDTSVariables90 variables = null;

    this.VariableDispenser.LockForRead("User::dimSrcId");

    this.VariableDispenser.GetVariables(out variables);

    dimSrcId = variables["User::dimSrcId"].Value.ToString();

    variables.Unlock();

    Thanks for your help.

    Vipul

    Tuesday, November 21, 2006 9:59 PM

All replies

  • To access the variables in code u need to use the Dispenser class given in Dts

    Ex.

    Dim vars As Variables
    Dts.VariableDispenser.LockOneForRead("<SSISVariable name>", vars)
    then use the value like this

    vars("<SSISVariable name>").Value

    if you want to modify the variable then lock the variable for Write like this


    Dts.VariableDispenser.LockOneForWrite("<SSISVariable name>", vars)

    Cheers

    Atul

    Tuesday, November 21, 2006 7:45 AM
  • Custom data flow components, also known as pipeline components, inherit from the PipelineComponent class. This exposes a public read-only property called VariableDispenser, so you can access this in your own methiods, or those you override from the base class, PipelineComponent. This gives you access to the variable dispenser to read or write variables as you desire.
    Tuesday, November 21, 2006 8:37 AM
    Moderator
  • Hi Atul

    Thanks for the reply.

    After making use of VaribleDispenser i am able to get System variables. But when i am trying to access User variables, i m getting this error:

    Error: 0xC0047062 at Data Flow Task, i2ADI [91672]: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to lock variable "User::sourceId" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    ---> System.Runtime.InteropServices.COMException (0xC0010001): Failed to lock variable "User::sourceId" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    The only difference which i could figure out was that System vars are defined at Package level scope, while the user variables are defined at Data Task Flow level. Can this be the issue?? Please let me know if you have some other pointers..

    Thanks,

    Vipul

    Tuesday, November 21, 2006 6:11 PM
  • Hi Vipul:

    A couple of things:

    My experience has been that variables are scoped to the object that is selected when you add the variable to the project. If you happened to have a Data Task selected when you add a variable, it's scope will be that Data Task. You can most definately create variables that are scoped to the entire package -- just make sure to click anywhere on the white surface in the Control Flow outside of any task object (this action should de-select any selected object) before adding a variable.

    You can verify a variable's scope in the Variables dialog. The Scope column contains either "Package" or the name of a task object (if the variable is scoped to a single object).

    You can scope variables to a container, such as a ForEach Loop or Sequence Container. Then, all the tasks within the container "see" the variable.

    Variable names are case-sensitive. In your example, you refer to "User::sourceId" -- you must have a variable named "sourceId", and not "SourceID"  or any of a zillion different ways to case the name.

    The code suggested by Atul should work for you.

    I always use the "ReadVariable" and "WriteVariable" functions presented by Daniel Read in his excellent article:

    http://www.developerdotstar.com/community/node/512

    Just some thoughts.

    Tuesday, November 21, 2006 7:14 PM
  • Hi Mike:

    Thanks for the reply. This is the my part of code which i m using.

    Microsoft.SqlServer.Dts.Runtime.Package pkg;

    Variables sourceIdVar = null;

    Microsoft.SqlServer.Dts.Runtime.VariableDispenser vd;

    pkg = new Microsoft.SqlServer.Dts.Runtime.Package();

    vd = pkg.VariableDispenser;

    vd.LockForRead("System::PackageName");

    vd.LockForRead("User::sourceId"); // <---- accessing this variable is throwing exception

    vd.GetVariables(ref sourceIdVar);

    foreach (Microsoft.SqlServer.Dts.Runtime.Variable myVar in sourceIdVar)

    {

    Console.WriteLine("Name : " + myVar.Name);

    Console.WriteLine("Description : " + myVar.Description);

    }

     

    Let me know your views on this part of code.

    Thanks,

    Vipul

    Tuesday, November 21, 2006 9:07 PM
  • Hi Mike:

    I was able to solve it. Code correction:

    IDTSVariables90 variables = null;

    this.VariableDispenser.LockForRead("User::dimSrcId");

    this.VariableDispenser.GetVariables(out variables);

    dimSrcId = variables["User::dimSrcId"].Value.ToString();

    variables.Unlock();

    Thanks for your help.

    Vipul

    Tuesday, November 21, 2006 9:59 PM
  • Hello

     if i need to show all system variable in me Custom component what should i do

    the code :

    Dim Var As IDTSVariables90 = Nothing

    Me.VariableDispenser.LockForWrite("System::StartTime")

    Me.VariableDispenser.GetVariables(Var)

    Var.Unlock()

    Monday, February 19, 2007 10:52 AM
  • Imports System
    Imports System.Collections.Generic
    Imports System.Text
    Imports Microsoft.SqlServer.Dts.Runtime
    
    
    Public Class ScriptMain
    
    	' The execution engine calls this method when the task executes.
    	' To access the object model, use the Dts object. Connections, variables, events,
    	' and logging features are available as static members of the Dts class.
    	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    
    	Public Sub Main()
        Dim pkg As Package = New Package()
        Dim vars As Variables = Nothing
        Dim variableDispenser As VariableDispenser = pkg.VariableDispenser
        Dim i As Integer
        Dim max As Integer
        Dim strVars() As String
        Dim strVarName As String
        Dim strVarValue As String
        ' Currently only displays System variables - still working on how to 
        ' access the User variables.
        max = pkg.Variables.Count
        MsgBox("Max is " & CStr(max))
        For i = 0 To max - 1
          strVarName = pkg.Variables.Item(i).Name
          strVarValue = pkg.Variables.Item(i).Value.ToString
          MsgBox(strVarName & "=" & strVarValue)
        Next
    
    
      End Sub
    • Edited by NiallBaird Thursday, January 13, 2011 12:42 AM Formatting
    Thursday, January 13, 2011 12:32 AM