I'm making a bunch of .dts jobs to move info between databases. I'm trying
to keep the DB names soft, so they can be user configured. So far, with the
Transform Data task, I've been able to use the Dynamic Properties task and
reset the Initial Catalog on the source and dest connections.

What I'm having trouble with is how to make the Execute SQL task use soft db
names.

- Rick

Why would you need to ? The ExecuteSQL task relates to a connection and you
have "Softed" these. Are you doing X DB stuff inside ?

--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Hi Allan, and thanks for asking the details.

Yes, the ExecSQL DOES relate to a connection, but only to 1 connection.
Because I'm transfering data from a source to a destination, and the SQL I
need to run needs to touch data in BOTH the source AND destination. Because
the ExecSQL task only relates to 1 connection, I need to specify a database
name in the actual SQL I'm executing.

I guess I don't know what X DB is...
A simplified example of what I'm doing would be:

ExecSQL Task Connection: Destination Connection
ExecSQL Task SQL Statement:
insert into table1
select A.field1, B.field2
from SourceDB.dbo.tableA A
join table2 B on A.fieldx = B.fieldy

In this case, it would be the SourceDB that I want to make soft.

- Rick

If you need to do that then either

1. Change the SQLStatement completely of the ExecuteSQL task
2. Build the statement in an Active Script task and let the DB name be a
global variable
3. Have a dedicated connection to the ExecuteSQL task and simply change
it's Catalog property.


--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org