|
scottpt
Posting Yak Master
USA
186 Posts |
 Posted - 12/15/2004 : 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
|
 |
|
DTS problem\challange
