Change Connection info and save

#1

We are restucturing our databases and I need to change a bunch of DTS
Packages' connection info. Is there a way to programatically loop
through all packages on the SQL 2000 Server and check for a particular
connection, if found then change it and then save the change? Either
with ActiveX scripting or VB6?

Thanks,

Jim
james_peppercorn (AT) storagetek (DOT) com

#2

Yep

Here is how to loop through packages in .Net

Enumerating DTS Packages using VB.Net
http://www.sqldts.com/default.aspx?6,105,250,0,1

You can now LoadFromSQLServer and then loop through the connections
collection of the package.

You are looking for connections with a ProviderID property of "SQLOLEDB"

--

----------------------------
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

"Jim" <james_peppercorn (AT) storagetek (DOT) com> wrote

Quote:

We are restucturing our databases and I need to change a bunch of DTS
Packages' connection info. Is there a way to programatically loop
through all packages on the SQL 2000 Server and check for a particular
connection, if found then change it and then save the change? Either
with ActiveX scripting or VB6?

Thanks,

Jim
james_peppercorn (AT) storagetek (DOT) com

#3

In article <0c1701c35c24$fa4611e0$a401280a (AT) phx (DOT) gbl>, Jim
<james_peppercorn (AT) storagetek (DOT) com> writes

Quote:

Thanks. I ended up modifying the "Transfer Packages
Sample Application" from SQLDTS.com in VB. I needed to
change more than just the connection in the DTS packages,
here's what I did:

============
Do While Not oPkgInfos.EOF
Set oPkg = New DTS.Package2
' Load the Pkg
oPkg.LoadFromSQLServer "SQLServerName", "", "",
DTSSQLStgFlag_UseTrustedConnection, "", "", "",
oPkgInfo.Name
' Change SQL Connections
For Each oConn In oPkg.Connections
If oConn.Catalog = "OldDatabaseName" Then
oConn.Catalog = "NewDatabaseName"
End If
Next
'Change Tasks
If oPkg.Tasks.Count > 1 Then
For Each oTask In oPkg.Tasks
If oTask.CustomTaskID = "DTSDataPumpTask" Then
oTask.Properties("DestinationObjectName")
= Replace(oTask.Properties
("DestinationObjectName"), "[OldDatabaseName]", "[NewDataba
seName]")
End If
Next
End If
' Save the Pkg
oPkg.SaveToSQLServer "SQLServerName", "", "",
DTSSQLStgFlag_UseTrustedConnection
oPkg.UnInitialize
Set oPkgInfo = oPkgInfos.Next
Loop
============

Jim
james_peppercorn (AT) storagetek (DOT) com

Jim, just a pointless piece of info but the "If oPkg.Tasks.Count > 1
Then" clause is redundant. The For Each will not enter the contained
code unless there is at least one instance of a task.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

#4

Quote:

Jim, just a pointless piece of info but the "If
oPkg.Tasks.Count > 1
Then" clause is redundant. The For Each will not enter
the contained
code unless there is at least one instance of a task.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Darren, very true. I think this issue came up when I was
figuring out whether to Dim oTask as dts.task or
dts.customtask. For some reason I was getting an error
and the IF statement fixed it. I'm tidying up the code as
I continue to find add'l items to change
from "OldDatabaseName" to "NewDatabaseName".

I've got about 30 local packages made with the DTS Wizard,
hard coding everything. Someday, I want to go back and un-
hard code as much as possible.

Jim

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Change Connection info and save

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Change Connection info and save - 08-05-2003 , 12:14 PM

Re: Change Connection info and save - 08-06-2003 , 01:30 AM

Re: Change Connection info and save - 08-06-2003 , 02:53 PM

Re: Change Connection info and save - 08-06-2003 , 03:50 PM