Help with Dynamic Sql Query

[Glowworm27]

Hello all,

I have a fairly simple SSIS package I am working with.
I have a user variable, "DatabaseName"
and another one called "DynamicSQL"

I have a script task that reads the variable 'DatabaseName" into as string.

Then I Write to the DynamicSQL using the database name as part of the sql query that I then write into the DynamicSQL variable.

Then in the DataFlow task, I set the OLE DB Source AccessMode to "SqlCommand from Variable" and set the SQlCOmmandVariable to "DynamicSQL"

However it is failing and complaining about some collection
"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."

Public Sub Main()
        'Assign all the Data Control queries to point at the proper database
        Dim sDbName As String

        sDbName = Dts.Variables("DatabaseName").Value.ToString

        Dts.Variables("DynamicSQL").Value = "Select * from " & sDBName.tostring & ".dbo.maintable

		
		Dts.TaskResult = Dts.Results.Success
    End Sub

The error seems to be pointing to the line where I set the string variable sDBName with the value in the DatabaseName DTS.Variables.

I was hoping to get this working as we have a developement and production server and want to make sure this is working on the developement server, then with a simple change to the DatabaseName variable I can get it to work on the production server simply by changing the name.

Thanks
G

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[RiverGuy]

Did you include "DatabaseName" in the ReadWriteVariables setting of your Script Task?

 

[williey]

Have you look at package configuration?

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[Glowworm27]

RiverGuy,

I saw the place where it looks like you can list the varaibles, but did not understand how they were used.

So in order to use a variable in a script task you need to define it in the readwrite or readonly variables of the script task?

That makes sense, I will check into it thanks.
G

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[RiverGuy]

So in order to use a variable in a script task you need to define it in the readwrite or readonly variables of the script task?

Yes, that is exactly right.