Accessing Variables in SSIS code

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

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

|||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.|||

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

|||

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.

|||

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

|||

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

|||

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()