| Westmain 2006-03-23, 1:31 pm |
| Hi,
I am using DTS to populate SQL Server databases from queries against an
Oracle database. I'm using what I'll call a 'master' script to truncate then
populate 74 SQL Server tables in 18 (as of now) different databases. These 18
databases all have the same tables, but will contain filtered data based on
some parameters. I'm using global variables to pass in values from another
package to the 'master' package. I am using in the queries in my 'master'
script to filter the data. All of this works fine.
The problem I'm having is that I need to call the 'master' package 18
times, each time writing to a different SQL database. The 'catalog' property
of my SQL Server connection object doesn't seem to be getting changed. I'm
passing in a global variable called 'gblSQLDB' and setting it's value to the
name of the SQL Database I wish to connect to. It is getting passed in
properly because I can display it in a MsgBox.
I have tried setting the property in an ActiveX script as follows:
oConnection.Catalog = DTSGlobalVariables("gblSQLDB").Value
and also tried using a Dynamic Properties Task and set the Destination
Property 'Catalog' to the global variable 'gblSQLDB'. Neither of these
methods work. The package, when called, will use the catalog that was set
when I last manually picked a Datbase in the Connection Properties dialog.
This of course causes my inserts to happen to the wrong database and results
in key violations, etc.
Any help with this matter would be greatly appreciated. The number of
databases I'm creating will eventually increase to over 300. Currently, I
have to change the Connection Database manually before I can run each
database.
--
Mike Silva
I.T. Specialist
Naval Undersea Warfare Center
Newport, RI
|