Technical Article

Set logging properties for group of DTS packages

,

This script will set the Logging properties for a group of DTS packages.

To run this script stand-alone, you'll need to set 6 variables. sSvr, sDb, sUid, sPwd, iSec and sPkgMatch. If you use this script in a DTS package, you can assign the values from Global Variables.

The sPkgMatch variable is used as a filter criteria on the package name when selecting records from msdb..sysdtspackages. If nothing is specified for this variable, all packages will be selected.

EXAMPLE: sPkgName = "Devel", will change all packages with a name that starts with 'Devel'.

Once it has the list of package names, it opens the package using the LoadFromSQLServer method, sets the Logging properties and saves the package.

NOTE: As the package is being modified programmatically, you will lose any text annotations and layout previously applied. Unfortunately there is no way to modify the package outside the DTS Designer and maintain these items.

Questions and comments welcome.

Phill

Option Explicit

Function Main()
On Error Resume Next

Dim oWsh ' Windows Scripting host object
Dim oConn ' ADO connection object
Dim oRS ' ADO recordset
Dim sSQL ' SQL string to execute
Dim sSvr ' server name
Dim sUID ' user id
Dim sPwd ' password
Dim iSec ' security mode
Dim oPkg ' DTS Package object
Dim sPkgName ' DTS Package name
Dim sPkgMatch ' String to match in package name
Dim iPkgCnt ' count of packages in list
Dim iPkgOk ' count of successfully changed packages
Dim iErr ' error count
Dim sMsg ' Message String

sSvr = "<your server name>"
sDb = "msdb"
sUid = "<your user id>" ' leave as empty string if using trusted connection
sPwd = "<your password>" ' leave as empty string if using trusted connection
iSec = 256 ' 0=SQL Security, 256=Trusted Connection
sPkgMatch = "<your pattern to match package names>"

' create windows script object so we can log message to event log
Set oWSh = CreateObject("WScript.Shell")
' setup ADO connection and recordset objects
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")

' build ADO connection string
sConn = "Provider=SQLOLEDB.1"
' check if using Integrated security or not
If iSec = 256 Then
' use integrated security, no username and password required
sConn = sConn & ";Integrated Security=SSPI;Persist Security Info=False"
Else
' use SQL security, so must provide username and password
sConn = sConn & ";Persist Security Info=True"
sConn = sConn & ";User ID=" & sUID
sConn = sConn & ";Password=" & sPwd
End If
sConn = sConn & ";Initial Catalog=" & sDb
sConn = sConn & ";Data Source=" & sSvr

' open ADO connection 
oConn.Open sConn

If Err.Number = 0 Then
' build SQL Statement to load list of packages
sSQL = "SELECT name FROM msdb.dbo.sysdtspackages WHERE name "
sSQL = sSQL & "LIKE '" & sPkgMatch & "%'"
' open recordset
oRS.Open sSQL, oConn

If Err.Number = 0 AND oRS.EOF = False Then
iPkgCnt = 0
iPkgOk = 0
While oRS.EOF = False
iPkgCnt = iPkgCnt + 1
' assign value in recordset to variable
sPkgName = oRS.Fields(0).Value
Set oPkg = CreateObject("DTS.Package")
oPkg.LoadFromSQLServer sSvr, sUID, sPwd, sSec, "", "", "", sPkgName
If Err.Number = 0 Then
oPkg.LogToSQLServer = True
oPkg.LogServerFlags = sSec
oPkg.LogServerName = sSvr
oPkg.LogServerUserName = sUID
oPkg.LogServerPassword = sPwd
oPkg.SaveToSQLServer sSvr, sUID, sPwd, sSec
If Err.Number = 0 Then
iPkgOk = iPkgOk + 1
sMsg = "Logging for DTS Package " & sPkgName & vbCrLf
sMsg = sMsg & " has been successfully repointed to the "
sMsg = sMsg & sSvr  & " server."
oWsh.LogEvent 4, sMsg
Err.Clear
Else
iErr = iErr + 1
sMsg = "ERROR while trying to save DTS Package " & sPkgName & vbCrLf
sMsg = sMsg & " after repointing logging to the "
sMsg = sMsg & sSvr  & " server." & vbCrLf
sMsg = sMsg & "Received following error:" & vbCrLf
sMsg = sMsg & Err.Number & ": " & Err.Description
oWsh.LogEvent 2, sMsg
Err.Clear
End If
Else
iErr = iErr + 1
sMsg = "ERROR while trying to load DTS Package " & sPkgName & vbCrLf
sMsg = sMsg & " to repoint logging to the "
sMsg = sMsg & sSvr  & " server." & vbCrLf
sMsg = sMsg & "Received following error:" & vbCrLf
sMsg = sMsg & Err.Number & ": " & Err.Description
oWsh.LogEvent 2, sMsg
Err.Clear
End If
Set oPkg = Nothing
oRS.MoveNext
Wend
Else
iErr = iErr + 1
sMsg = "ERROR while trying to retrieve list of Packages using "
sMsg = sMsg & " SQL statement " & vbCrLf & sSQL & vbCrLf
sMsg = sMsg & "Received following error:" & vbCrLf
sMsg = sMsg & Err.Number & ": " & Err.Description
oWsh.LogEvent 2, sMsg
Err.Clear
End If
Else
iErr = iErr + 1
sMsg = "ERROR while trying to establish database connection "
sMsg = sMsg & " using following connection string " & vbCrLf & sConn & vbCrLf
sMsg = sMsg & "Received following error:" & vbCrLf
sMsg = sMsg & Err.Number & ": " & Err.Description
oWsh.LogEvent 2, sMsg
Err.Clear
End If

If iErr = 0 Then
sMsg = "Repointing of DTS Package logging completed." & vbCrLf
If iPkgCnt > 0 Then
sMsg = sMsg & "Successfully changed " & iPkgOk 
sMsg = sMsg & " out of " & iPkgCnt & " packages." & vbCrLf
End If
sMsg = sMsg & "Check Windows Application Event log for detailed messages."
oWsh.LogEvent 4, sMsg
Main = DTSTaskExecResult_Success
Else
sMsg = "NOTE: " & CStr(iErr) & " errors occurred while trying to "
sMsg = sMsg & "repoint DTS Package logging." & vbCrLf
If iPkgCnt > 0 Then
sMsg = sMsg & "Successfully changed " & iPkgOk 
sMsg = sMsg & " out of " & iPkgCnt & " packages." & vbCrLf
Else
sMsg = sMsg & "No packages were changed!!" & vbCrLf
End If
sMsg = sMsg & "Check Windows Application Event log for detailed messages."
oWsh.LogEvent 1, sMsg
Main = DTSTaskExecResult_Failure
End If


End Function

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating