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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DTS problem\challange
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

186 Posts

Posted - 12/15/2004 :  13:47:33  Show Profile  Reply with Quote
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.


United Kingdom
12543 Posts

Posted - 12/15/2004 :  14:08:42  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 12/15/2004 14:09:10
Go to Top of Page

Posting Yak Master

186 Posts

Posted - 12/15/2004 :  21:13:02  Show Profile  Reply with Quote
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


United Kingdom
12543 Posts

Posted - 12/15/2004 :  21:28:45  Show Profile  Visit nr's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.37 seconds. Powered By: Snitz Forums 2000