Is there a method for passing the database name as a variable to a DTS package
We have all item databases named as the business date i.e. 20040426AllItem and I'm manually having to re-select the database to run the DTS packages as I haven't found a way of passing the db name (I've also tried to incorporate the USE statement in the tranform data task query to derive the db name here but also without success).

You can use the Dynamic Properties Task to take a variable and set it to the
DB (Catalog) property of your connection. However by default the
SourceObjectname property of a DataPump task uses the three part name. You
can remove the DB part through Disconnected Edit to remove this "hard coded"
DB, or just use a (Source SQL Statement) SELECT statement that dos not
include the DB.

Conversely you can manipulate the SELECT SQL though an ActiveX Script Task
to include the DB variable such that it overrides the connection DB, using
the three part name in your SQL.


--
Darren Green
http://www.sqldts.com

"Peter" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I haven't found a way of passing the db name (I've also tried to incorporate
the USE statement in the tranform data task query to derive the db name here
but also without success).

You have to create a global variable in your DTS package and set its value to conection property 'Catalog' for example in ActiveScript task, something like that
oPkg.Connections("YourServerName").properties("Cat alog").value = DTSGlobalVariables("YourGlobalVariableName").Valu
or using Dynamic Property Tas

MNdreu