Hi

I need to rewrite several dts packages so that they can be easily
transferred between dev, test and live.

Most of the settings I've put into an INI file, and I use a Dynamic
Properties Task to set the necessary before executing. That works fine
for all the source files, constant values, etc. The problem is the
destination connections for the data pump tasks. If I change the UDL
path of the connection dynamically, this does not affect the Transform
Data Task destination. ie, the connection is changes, but the
desination tab of the data pump task still points to the old table (&
database).

I have dev and test databases on the same server, and live on a
different server. So from Dev to Test, I change the UDL file in the
dynamic properties task. I would expect the Table Name on the
Destination tab of the Data Pump task to change from:
[dev].[dbo].[tablename]
to
[test].]dbo].[tablename]

but it just stays the same (as it can still find the [dev] database on
this server - so i get the list of all the table names in the [test]
database, but the darn thing is still pointing at the [dev] table.)

I have MANY connections and data pumps like this, so I would hate to
have to go through and set up each one dynamically somehow. I would
have though, since they all reference an "Existing Connection", that I
could just change the main connection and they would all follow
suit...

does this make sense?


Thanks for any help
Sean

The issue is with the DataPump task SourceObjectname or
DestinationObjectName properties. You are only concerned with the
DestinationObjectName, but the behaviour is the same for both. These
properties use the three part name for the object ([db].[owner.[object]),
and the DB is not updated in line with the connection. This can be quite
useful, but also a pain as you have found out.

Since the DB is just part of the object identifier you cannot use the
Dynamic Properties Task to change this, but it can be done in ActiveX
Script.

Alternatively you can just remove the DB qualifier and go with
[owner].[object] only. This works fine, and means the table referenced is
always that of your connection. Simplest way to do this is to edit the
property once via Disconnected Edit. If you have a lot of packages then you
could write a script to perform the changes for you.


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



"Sean Walsh" <plugNOwalshSPAM (AT) yahoo (DOT) com> wrote