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

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

scottpt
Posting Yak Master

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

nr
SQLTeam MVY

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.
http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html

Take the bit out of here to save the package after the change.
http://www.nigelrivett.net/s_LoadPackageToServer.html

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).
http://www.nigelrivett.net/ScriptDTSProperties.html

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

scottpt
Posting Yak Master

USA
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

sServerName = SQLDBSERVERPROD

Set objCon = CreateObject("ADODB.Connection")

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

objCon.CursorLocation = 3 'adUseClient
objCon.Open

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")
X=0

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
X=1
'objFStream.WriteLine "connect trusted = " & objDTSConnection.usetrustedconnection
objDTSConnection.usetrustedconnection=1
'objFStream.WriteLine "connect trusted = " & objDTSConnection.usetrustedconnection
End IF
Next

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

Set objDTSPackage = Nothing

objRsPackageNames.MoveNext
Loop

objFStream.Close
Set objFStream = Nothing

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

Main = DTSTaskExecResult_Success
End Function
Go to Top of Page

nr
SQLTeam MVY

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  
 New 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.09 seconds. Powered By: Snitz Forums 2000