Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DTS problem\challange

Author  Topic 

Posting Yak Master

186 Posts

Posted - 2004-12-15 : 13:47:33
Take a bunch o' packages stored in SQL and automatically open every one up and switch every connection object from SQL authenticated to Windows authenticated.


12543 Posts

Posted - 2004-12-15 : 14:08:42
This will load a package. Just need to go through all packages in msdb..sysdtspackages.

Take the bit out of here to save the package after the change.

Now all you need to do is find the connections and change the authentication method.
You will need to loop through all the connections in the package.

This does that (in fact this loads all the packages so is probably better as a start than the above - it's a vbscript task in a dts package but would be better in VB for your purposes).

Near the top of he scripting is Connections
It doesn't include the authentication but that should be easy to find - especially in VB.
Then just save the package.

p.s. Usually lots easier to save a load packages from files rather than sql server.

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Posting Yak Master

186 Posts

Posted - 2004-12-15 : 21:13:02
Nigel your awsome. Here is the code I put together using your examples.

' Visual Basic ActiveX Script

Function Main()

Dim objCon
Dim objCmd
Dim objRsPackageNames

Dim objFileScript
Dim objFStream

Dim sServerName

Dim objDTSPackage
Dim objDTSTask
Dim objDTSConnection
Dim objDTSTransformation
Dim objDTSDataPumpTask
Dim objDTSGlobalVariable
Dim objDTSCustomTask
Dim objDTSProperty

Dim objDTSDynamicPropertiesTask
Dim objDTSDynamicPropertiesTaskAssignment


Set objCon = CreateObject("ADODB.Connection")

objCon.ConnectionString = "Provider=sqloledb;" & _
"Data Source=" & sServerName & ";" & _
"Initial Catalog=" & "msdb" & ";" & _
"Integrated Security=SSPI"

objCon.CursorLocation = 3 'adUseClient

Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = objCon
objCmd.CommandType = 1 'adCmdText
objCmd.CommandText = "select distinct name from sysdtspackages order by name"

Set objRsPackageNames = CreateObject("ADODB.Recordset")
Set objRsPackageNames = objCmd.Execute

Set objFileScript = CreateObject("Scripting.FileSystemObject")
Set objFStream = objFileScript.CreateTextFile("c:\Replace.txt")

Do While Not objRsPackageNames.EOF
' ScriptDTSPackage2 objRsPackageNames("Name"), sPath, sServerName, iTrusted, sUserName, sPassword

' load package
Set objDTSPackage = CreateObject("DTS.Package2")
objDTSPackage.LoadFromSQLServer sServerName, , , 256, , , , objRsPackageNames("Name")

objFStream.WriteLine "Package=" & objRsPackageNames("Name")

For Each objDTSConnection In objDTSPackage.Connections
'objFStream.WriteLine "<ID=" & objDTSConnection.ID & ">" & "<name=" & objDTSConnection.Name & ">" & "<Source=" & objDTSConnection.DataSource & ">" & "<ProviderID=" & objDTSConnection.ProviderID & ">"
IF objDTSConnection.usetrustedconnection=0 THEN
'objFStream.WriteLine "connect trusted = " & objDTSConnection.usetrustedconnection
'objFStream.WriteLine "connect trusted = " & objDTSConnection.usetrustedconnection
End IF

IF X <> 0 then
objDTSPackage.SaveToSqlServer sServerName,,,256
End if

Set objDTSPackage = Nothing


Set objFStream = Nothing

Set objRsPackageNames = Nothing
Set objCmd = Nothing
Set objCon = Nothing

Main = DTSTaskExecResult_Success
End Function
Go to Top of Page


12543 Posts

Posted - 2004-12-15 : 21:28:45
Well done.
Nice to see you only needed/wanted a hint.

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

- Advertisement -