| Darren Green 2005-07-20, 8:24 pm |
| In message <457E1827-A22C-41E8-9578- 3FB70B4AC976@microso
ft.com>, SQL
Junior <SQL@Junior.?.microsoft.com.invalid> writes
>for DTS PackageHi
>
>I have created many DTS Packages that use Send Mail Task to send email to
>abc@test.com, how can I change them to abc@test1.com without manually
>changing it (too tedious). Is there any like replace function or utility for
>DTS Package that can replace test.com to test1.com.
>
>Thanks for any advice
Write some code like this, (not checked).
Enumerate packages, loading each one. Enumerate tasks, looking for send
mail tasks. Grab the custom task object and set the to address.
Dim oDTSApp As DTS.Application
Dim oPkgSQLServer As DTS.PackageSQLServer
Dim oPkgInfos As DTS.PackageInfos
Dim oPkgInfo As DTS.PackageInfo
Dim oPkg As DTS.Package2
Dim oTask As DTS.Task2
Dim oMailTask As SendMailTask
' Enumerate Packages
Set oDTSApp = New DTS.Application
Set oPkgSQLServer = oDTSApp. GetPackageSQLServer(
"SourceServer", "",
"", DTSSQLStgFlag_UseTru
stedConnection)
Set oPkgInfos = oPkgSQLServer.EnumPackageInfos("", True, "")
' Loop through Packages
Set oPkgInfo = oPkgInfos.Next
' "Column does not exist" error on SQL 7.0 because sysdtspackages
lacks packagetype column
Do While Not oPkgInfos.EOF
Set oPkg = New DTS.Package2
' Load the Pkg
oPkg.LoadFromSQLServer "SourceServer", "", "",
DTSSQLStgFlag_UseTru
stedConnection, "", "", "", oPkgInfo.Name
' Change SQL Connections
For Each oTask In oPkg.Tasks
If oTask.CustomTaskID = "DTSSendMailTask" Then
Set oMailTask = oTask.CustomTask
oMailTask.ToLine = "spam@sqldts.com"
End If
Next
' Save the Pkg
oPkg.SaveToSQLServer "DestServer", "", "",
DTSSQLStgFlag_UseTru
stedConnection
oPkg.UnInitialize
Set oPkgInfo = oPkgInfos.Next
Loop
' Clean Up
Set oMailTask = Nothing
Set oTask = Nothing
Set oPkg = Nothing
Set oPkgInfo = Nothing
Set oPkgInfos = Nothing
Set oPkgSQLServer = Nothing
Set oDTSApp = Nothing
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org
|