Hi,
I created a DTS package to do some ETL task between 2 SQL servers. I
want to move this package to do a similar ETL task between another set
of databases (the names of the databases are different however the
table names are the same .. test and production databases) on another
server. I found that the server names and database names can be
dynamically set using a dynamic properties task in dts. However all the
insert/update/lookup queries as well as the source and destination
object names inside the dts package have the names of the test
databases hard coded in them. isnt there a way t ohave a one place
change so that i can easily move the dts package from the test database
to production database.
please advice.
regards,
Atul.

Yep.

There are a number of ways.

1. Set it up so that you assign to the connection the name of the DB
and to the tasks (SourceObjectName, DestinationObjectName) you assign
the <owner>.<tablename>.

2. Get rid of the databasename from the off.

Changing the DataPump Source and Destination Tables
(http://www.sqldts.com/default.aspx?213)


Allan



"atulpandey (AT) gmail (DOT) com" <atulpandey (AT) gmail (DOT) com> wrote

let me see if i got it right .. I use the SQL server 2000 enterprise
manager console to edit my DTS package using the "Disconnected Edit"
feature
so i should do the following
1) in all my "data driven query tasks" and "data pump tasks" i should
get rid of the database name from all the insert / update /lookup
querries and leave it in a <owner>.<tablename> format ..
2) I did not understand the SourceObjectName and DestinationObjectNames
assignment thing .. are <owner> and <tables> , variables that can be
assigned from an INI file etc...
I have close to 50 tasks in the same DTS package. i tried setting the
SourceObjectName and DestinationObjectNames in an INI file for all the
tasks but dts did not support the INI file size and that solution did
not work.

how can I replace the hardcoded names in SourceObjectName and
DestinationObjectNames with variables which can be set dynamically?
regards,
Atul.

So you want ALL names to be dynamic? In this instance if you have 50
datapump tasks then that is 100 objects.

Using the DP task to read from an external source to populate these
values directly then you will need 100 entries in say the DB table. Yes
it can be done.

I personally prefer to use a DB Table for the source of my
parameterisation data.



"atulpandey (AT) gmail (DOT) com" <atulpandey (AT) gmail (DOT) com> wrote

actually the only thing thats changing in my case is hte name of the
database .. however each query in the dts data driven query task and
the data pump task has the database name in it as
<databaseName>.<ownerName>.<tableName>
If i simply remove the database name form each of these tasks will the
dts package still work fine ?
regards,
Atul.

Should do yes. The Connection is pointing to the DB so you really do
not need it in the other places as well.

"atulpandey (AT) gmail (DOT) com" <atulpandey (AT) gmail (DOT) com> wrote