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

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

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