Hi

I'm using DTS to migrate data between excel and SQL database. I have
created global variables for setting up the values for source excel and
destination database server, database name, user id and password. I've
added a dynamic properties task and have assigned the global variables
properly to source and destination connection properties. Changing the
destination server at run time works fine, but not changing the
destination database.

Say I have created the package with destintaion as Northwind1 and I have
created my transform data task properties between excel and the tables
in Northwind1. Now when I want to run the DTS with a different
destination , say Northwind2, by setting the global variable of
destination database, I get unspecified error. I could see that this
problem is because, when the package was created, DTS by default adds
the database name to the table . So in the package the mapping is
between excel table and Northwind1.dbo.tablename. So even if I try to
change the destination database to Northwind2, only the connection
property seems to change, but the Transformdatatask property still refes
to Northwind1. If I try to open the task now, I get an error Invalid
object name Northwind1.dbo.tablename.

As a workaround, I then created global variables for each of the
destination tables and assigned these to the DestinationObjectName for
each of the tasks. When assigning the values for the global variables,
only table name was set, no database was appended With this I was able
to change the destination database and when I opened the transform data
task now I could see that no database was appended.

Is there a better approach to do this?

Thanks

rjn






*** Sent via Developersdex http://www.developersdex.com ***

You've got it, the SourceObjectName and DestinationObjectName need to be
taken care of, and your method sounds fine.

Normally I need to change the database but the table name stays the
same, so I just manually remove the database qualifier from the object
name through Disconnected Edit, and that way I only need to manage the
"Initial Catalog" property on the connection.

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

RJN wrote:

Thanks. That worked though I was bit skeptical to work on the
disconnected edit mode.



*** Sent via Developersdex http://www.developersdex.com ***