.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Can multiple branches of a package use the same variables

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Can multiple branches of a package use the same variables. I don't want to create new variables for each branch .I am getting an error in one of the script tasks  "The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."  Assuming I dont have a naming error  what else should I look for.

View Complete Post

More Related Resource Links

Creating SSIS Package Variables Programmatically

Hi There, I am fairly new to SSIS.  I am creating a number of custom SSIS Tasks that I want to be able to share information between at runtime.  I figured that the best way to do this was through package variables, as in the Execute() method each custom task has access to the package variables through the VariableDispenser object.  I have managed to read variables at runtime that I create in the designer, however I am having trouble creating and writing to them.  I have created a very simple custom task below that I would expect to create the specified variable... [DtsTask(DisplayName = "TestVariableWriter")] public class Variab : Task { public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction) { try { string varName = "TestVariable"; string varValue = "TestValue"; Variables vars = null; variableDispenser.LockForWrite(varName); variableDispenser.GetVariables(ref vars); if (variableDispenser.Contains(varName)) vars[varName].Value = varValue; else vars.Add(varName, false, string.Empty, varValue); vars.Unlock(); return DTSExecResult.Success; } catch (Exception ex) {

Unable to build connection string using Package Level variables from the AcquireConnection fonction

Hi all! In SSIS 2008, within a script component trying to refer to a package level variables that was added to the ReadOnlyVariables collection of the component, the following fails within the AcquireConnnection function and I get this error: Error 1 Validation error. Test1: Test1: Microsoft.SqlServer.Dts.Pipeline.ReadOnlyVariablesNotAvailableException: The collection of variables locked for read access is not available at this point.     à Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     à Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction)     à Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)   string connectionString = "DSN=" + this.Variables.MYDSN; Conn = new OdbcConnection(connectionString); Conn.Open();     If I move the previous code in the PreExecute function, it runs fine though.   Then, if I move the connectionString variable to the class definition, and populate it from within the PreExecute function and leave the last 2 lines in the AcquireConnection function, I get the following error: Error 1 Validation error. Test1: Test1: System.InvalidOperationException: The ConnectionString property

accessing package level variables in a Script Component in 2008 version


how to access the package level variables within a script component in SSIS 2008

start SSIS package via script task with package configuration via variables



it is possible to start ssis packages with dtexec and set variables as package configuration like this:

dtexec.exe /SQL "\mart1\import_fact_calls" /SERVER "R08" /SET \package.variables[FullImport].Value;True

Now I'm trying to start a package via script task with this code:

Public Sub Main()
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    If Dts.Variables("package").Value <> "" Then
      pkg = app.LoadFromSqlServer(Dts.Variables("package").Value, "R08", "USER", "PASS", Nothing)
      pkgResults = pkg.Execute()
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

How can I add a package configuration like the one with dtexec to the script code?

I like any ideas, links, docus ... Thanks!

Package Configuration on multiple environments


Hello Experts,

I have enabled package configurations to Sql Server, so that it would help me to change the connection properties through TSQL.

I have created these packages on 32bit OS (Windows XP), now i facing problem while running these packages on Win 2008 64bit OS.

though it connects to DB and picks up configurations, it's still giving me "connection failed validation" (i have four file folder connection managers)

How should i over come this issue ? please help me


Thanks, Krishna Nellutla

Package configuration file with multiple connection strings


I am using the configuration file to read the connection string in the SSIS packages. In one of my packages i have to use two different connection strings. If i specify the second connection string in the config file, then the current package works well but other packages fails during validation saying the second connection manager does not exist in the package. Is it not possible to have multiple connection string in a single config file?

Run a package for multiple servers (configurations)


I have a package that collects information from an SQL Server about the databases on that server. I store this information in a table from where I can create graphs (for example the disk usage, etc.). Now I would like to run this package for about 20 servers. The only difference in the package is the connection of the source DB. What would be the easiest way to do this:

1. Build a loop in the package that runs 20 times and putting the server name in a variable

2. Set package configurations that it runs 20 times with every time a different value for the server of the read db component (is that even possible??)

3. Another option that I don't know of yet?




Session variables are accessible to multiple users?


I have an asp.net website in which users can submit reports. Another tester and I tested this page at the same time and when I went to review the information I submitted, it displayed information that I didn't submit. It displayed the info submitted from the other user.

I use session variables for these values. I don't understand how this happened?

What can I do so each user can individually submit their information without overlapping some one else's information? The plan is for multiple users to be able to use this page to submit their weekly layer reports.

The first example of code is how I add the session variables from the textboxes.

If Session("BarnNo") = Nothing Then
                                'add session variables
                                    Session.Add("FName", FNameTextbox.Text)
                                    Session.Add("LName", LNameTextbox.Text)
                                    Session.Add("WkEnd", Calendar1.SelectedDate.ToShortDateString())
                                    Session.Add("DisplayDate", Calendar1.SelectedDate.ToLongDateString)

                                    Session.Add("BarnNo", BarnNumberTextbox.Text)
                                    Session.Add("FlockNo", FlockNoTextbox.Tex

Sharing the variables in a master package,



I have created an SSIS package that calls other packages.

This master ssis package contains a variable say MyVariable.

In the child packages I use this MyVariable in an expression to build a connection string.

@[User::MyVariable] + "Something"

This works fine, however each time I open the child packages - a couple of errors are shown in the errors pane notifying me that the variable does not exist in the child package. This is correct because it does not - however at runtime the child package can make use of the variables in the parent ssis package.

So how can i supress these errors?


I.W Coetzer

Extracting data from multiple CSV files using SSIS package



I am new to SSIS. I am extracting data from multiple CSV files using SSIS package and copying that data into SQL server 2008  database. Can you please confirm if I can create only one package to handle all CSV files or I will have to create packages for each CSV file.

Column names and numbers are different in all CSV files. 







Accessing Package variables at design time


Hello All,

I have a question relating to the variables in SSIS.

I have an existing etl package with data flow components. One of the column is System_id to identify the Source System, the package is pulling the data from.  I plan to create a component which will validate the data based on the Rules stored in the database. These rules will be accessed based on the System_id + Target combination which needs to be set by the user at design time when the component is dropped.

In design time, i want to access this System_id field for the Custom data flow component so that i can access the database table and display the available Targets for this System_id which the user can set so that i can run the rules against this data.

I understand that i cannot access the value of the data flow at design time. My question is there other way, where i can set the variable value and access it at design time ?

Please let me know







Get list of package variables in SSIS code


Hi all,

i need to get list of package variable names during execution of a custom control flow task.

Best regards Crom

How to Create multiple flat file import package?

Hello Everyone,

I am an absolutely beginner to SSIS and need to perform the following task.

  • Need to import Master data from (semi colon) ; delaminated flat files into separate tables in SQL Server
  • Each file will create a separate table in SQL Server
  • While importing I need to perform validation on each file and only extract data that is valid e.g. CNIC column must be numeric and 13 digits long.
  • Any data that is not valid will be sent to a separate table with same TableName_bad suffix
  • After the data is extracted I will extract the details data of only those records that have been extracted in master table before

I know about basic data flow controls etc but I don’t know which control will be better for which task. Please tell me what will be the procedure to fulfill my requirements. I will be extremely thankful.



Syed Afraz Ali


SSIS - Variables sharing from child to parent package



I'm a having a problem in passing the value of a variable from child package to the parent package in SSIS.

Can anyone please help me??? Any process would be helpful..either using configurations or using scripting (c# or vb.net)..



Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

Dts Package Variables Works in ScriptMain but Not DtsUtility.cs


I have a few useful snipits that I had preferred to keep in a helper file named DtsUtility.cs

The identical code for the Read/Write variables was working in ScriptMain in one task, but again rather than paste that into the ScriptMain for each task I wanted to add the on cs file to each task.

The error essentially is that the variables cannot be found at line, vd.LockOneForWrite(varName, ref Vars);

ScriptMain snipit...

        using DtsUtility;
        ...........omitted code......
        if (dsX.Tables[0].Rows.Count >= 1)
        ParaID = (Guid)dsX.Tables[0].Rows[0]["ParaID"];
        CaseNo = (string)dsX.Tables[0].Rows[0]["CaseNo"];
        sXML = (string)dsX.Tables[0].Rows[0]["OrderXML"];
        DtsUtil u = new DtsUtil();
        bSucess = u.WriteDTSVar("RecID", RecID);
        bSucess = u.WriteDTSVar("ParaID", ParaID);
        bSucess = u.WriteDTSVar(&

One SSIS package - multiple configurations?

I'm looking for a basic guideline/best practices for creating an SSIS setup involving one "master" package called with one variable that will link it to a set of configurations based on that variable. Currently we create a new package for each client, each with their own configuration. Now each package is basically the same but we create an entirely new package file for each customer. There are variables that are of the YES/NO variety to enable/disable specific functions based on client need. As it is now, if we improve the package (a new version if you will) we have to go back to all prior packages to update and make the adjustment. These can be anything from a small SQL code change to an entirely new container of tasks. Either way, you get the idea, this is very inefficient and I am attempting to standardize our packages and process. If anyone could post a few links or point me in the right direction it would be very much appreciated. Thank you!
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend