DTS problem\challange - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. 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

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-15 : 14:08:42

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.

scottpt
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

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

nr
SQLTeam MVY

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources