Changing SQL Server connection

Question

Changing SQL Server connection

StefanJ

SSCarpal Tunnel

Points: 4713
More actions
September 22, 2003 at 9:32 am

#106297

I am trying to use a parent package to call many child packages. Purpose is to populate SQL Server from legacy systems.
I need to be able to change the Connection in the parent package from TestDB to ProductionDB.
I thought this was possible by setting up global variables in the parent and child packages. Right mouse clicking on Execute Package Task properties to set the global variables. Then setting up a Dynamic Properties Task or ActiveX Script Task to change the SQL.Catalog database name.
However when testing the switching it took me some time to discover what the problem was. Execute Task SQL changes the connection fine, BUT Transform Data Task 'Destination' table name is prefixed with the database name and does not reflect a change to the global variable.
Is there a way round this?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 1

I am using the code below to change the connection. Do you have an example how I would use ActiveX to change the table names.

Function Main()

dim PKG

dim SQL

set PKG = DTSGlobalVariables.Parent

set SQL = PKG.Connections("SQLServerConnection")

SQL.DataSource = DTSGlobalVariables("SQLServerName").Value

SQL.Catalog=DTSGlobalVariables("DatabaseName").Value

Main = DTSTaskExecResult_Success

End Function

philcart SSC-Forever Points: 47797 More actions · Answer 2

I've modified your script so that it changes the table name as well as the connection properties. You wil need to put in the name of your datapump task where indicated in the code. If you have more than one datapump task, just open and modify each task using the same method.



Function Main()



dim PKG

dim SQL

dim Tsk

dim Tbl



set PKG = DTSGlobalVariables.Parent

set SQL = PKG.Connections("SQLServerConnection")

Set tsk = Pkg.Tasks("<your datapump task name>")



SQL.DataSource = DTSGlobalVariables("SQLServerName").Value

SQL.Catalog=DTSGlobalVariables("DatabaseName").Value



' the next three statements could be done in one but

' it's clearer when broken up like this

' this will retrieve the table name from the task

Tbl = Tsk.Properties("SourceObjectName").Value

' remove the current database name

Tbl = Right(Tbl, Len(Tbl)-Instr(1,Tbl, "."))

' add the new database name

Tbl = DTSGlobalVariables("DatabaseName").Value & "." & Tbl

' assign the new value back to the property

Tsk.Properties("SourceObjectName").Value = Tbl



Set Tsk = Nothing

Set SQL = Nothing

Set Pkg = Nothing



Main = DTSTaskExecResult_Success



End Function

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 3

phillcart, thanks very much for your very helpful reply. Problem I am finding with DTS is that none of the books I have go in to sufficient detail.

Hopefully there are more examples in the best of SQL Server Central which I recently ordered.

philcart SSC-Forever Points: 47797 More actions · Answer 4

My bible for DTS is SQL Server 2000 DTS published by Wrox. I also have the DTS object model poster that Lumigent produced.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface