Any idea how can I create a global variable to change the
database name in the source and destination tab of the
tranformation (the database name part only - my_db.table1
to my_other_db.table1)?

I know I can create the global variable to change the name
of the database in the tranformation connection but that
does not change the database name in the source and
destination.

Thanks in advance for any help.

Before diving into your issue, I have noticed that when Microsoft creates the target table in a transformation, it appends the database name. I would think that simply using TableName or dbo.TableName syntax would allow you to move the package from one database to another by simply changing the connection properties

You can change the source table name and the target table name in the disconnected edit menu option to change the source table name so that it has the syntax dbo.TableName, removing the database name entirely. You have to find the task (not the step) that corresponds to your transformation. Then you must find the SourceObjectName and the DestinationObjectName. You remove reference to the database and let DTS use the package connection information

Or you can use an activex script

'************************************************* ********************
' Visual Basic ActiveX Scrip
'************************************************* **********************

Function Main(

dim myTas
set myTask = DTSGlobalVariables.Parent.Tasks("Copy Data from DeclineList to [RussExperimental].[dbo].[DeclineList] Task"
dim sTabl
dim sD
dim iPo
sTable = myTask.CustomTask.DestinationObjectNam
sDB = DTSGlobalVariables("DestinationDB").Valu
iPos = instr(1,sTable,"."
if iPos > 0 the
myTask.CustomTask.DestinationObjectName = sDB & mid(sTable,iPos
end i

' Do the same with the SourceObjectNam

Main = DTSTaskExecResult_Succes
End Functio

Russel Loski, MCSD

Russel, Thank you so much. I changed the
DestinationObjectName and SourceObjectName for the tasks
in the disconnect edit menu choice and it works great.
It's a great help already but is there anyway I can have
that as default so I don't have to change for every
DTSTask_DTSDataPumpTask_1
DTSTask_DTSDataPumpTask_2
DTSTask_DTSDataPumpTask_3, etc

Now, I'm working in the ActiveX Script. Thanks again!

appends the database name. I would think that simply
using TableName or dbo.TableName syntax would allow you to
move the package from one database to another by simply
changing the connection properties.
source table name so that it has the syntax dbo.TableName,
removing the database name entirely. You have to find the
task (not the step) that corresponds to your
transformation. Then you must find the SourceObjectName
and the DestinationObjectName. You remove reference to
the database and let DTS use the package connection
information.
Task")

In message <E15550B3-B3A6-4AE7-82C4-E72C2932169F (AT) microsoft (DOT) com>,
RussLoski <anonymous (AT) discussions (DOT) microsoft.com> writes
<snip>
Just to confirm, the method you describe works fine.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org