Post reply

Package Log

  • January 19, 2004 at 3:19 pm

    #60049

    I have more than 100 DTS packages, is there a easy way to find if logging is enabled in all the DTS packages??

    Thanks

  • January 21, 2004 at 3:41 pm

    #491047

    The sysdtspackagelog table in msdb contains package log data.

    Run this in Query Analyzer to show the names of packages that have been executed with logging enabled:

    use msdb

    select name from sysdtspackagelog

    If a package name is listed more than once, it's because it has multiple versions with logging enabled.

     

     

     

     

    Greg

  • January 22, 2004 at 10:37 am

    #491161

    If the package does not have any versions it may not be in sysdtspackagelog right? I get no records when i run the query.

    Thanks for your input.

  • January 22, 2004 at 9:40 pm

    #491260

    Firstly, every package has at least one version. However, there may be package log records for versions that don't exist. If you don't get any records when executing that query then none of the packages have package logging enabled.

    Try this VBScript that produces a list of the package logging properties for each package on a given server. Set the items enclosed with <> to what you need before running the script. 

    Option Explicit

Dim oPkg ' DTS Package object 
Dim oSrvr ' SQL-DMO server object 
Dim oDb ' SQL-DMO database object 
Dim oQry ' SQL-DMO Query results object 
Dim sSQL ' SQL string to execute 
Dim sSrvrName ' Server name that holds package 
Dim sSrvrUID ' login used to access server that holds package 
Dim sSrvrPWD ' password used to access server that holds package 
Dim iSrvrSec ' security mode used to access server that holds package 
Dim sPkgName ' DTS Package name 
Dim sPkgID ' DTS Package id 
Dim sPkgVer ' DTS Package versionid 
Dim sMsg ' Message String 
Dim oFS ' Filesystem object 
Dim oTS ' TextStream object 
Dim iRow ' row number in Query results object 

Set oFS = CreateObject("Scripting.FileSystemObject") 
Set oTS = oFS.CreateTextFile("< your output file >", True) 
oTS.WriteLine "Started : " & Now() 

' select distinct list of packages which 
' only loads the latest version 
sSQL = "SELECT DISTINCT name " 
sSQL = sSQL & "FROM msdb.dbo.sysdtspackages " 
sSQL = sSQL & "ORDER BY name" 
Set oSrvr = CreateObject("SQLDMO.SQLServer") 
sSrvrName = "< your server name >" 
iSrvrSec = 256 ' 0 = SQL Security, 256 = Windows Authentication 
If iSrvrSec = 0 Then 
' SQL Security 
sSrvrUID = "< your login >" 
sSrvrPWD = "< your password >" 
oSrvr.LoginSecure = False 
Else 
' Windows Authentication 
sSrvrUID = "" 
sSrvrPWD = "" 
oSrvr.LoginSecure = True 
End If 

' following two lines are not required if LoginSecure is true 
oSrvr.Login = sSrvrUID 
oSrvr.Password = sSrvrPWD 

' connect to the server 
oSrvr.Connect sSrvrName 
Set oDb = oSrvr.Databases("msdb") 
Set oQry = oDb.ExecuteWithResults(sSQL) 

For iRow = 1 to oQry.Rows 
sPkgName = oQry.GetColumnString(iRow, 1) 
' load the package 
Set oPkg = CreateObject("DTS.Package") 
oPkg.LoadFromSQLServer sSrvrName, sSrvrUID, sSrvrPWD, iSrvrSec, "", "", "", sPkgName 
' get package logging properties 
sMsg = "Package:" & sPkgName 
sMsg = sMsg & " LogToSQLServer:" & oPkg.LogToSQLServer 
sMsg = sMsg & " LogServerName:" & oPkg.LogServerName 
sMsg = sMsg & " LogServerFlags:" & oPkg.LogServerFlags 
oTS.WriteLine sMsg 
Set oPkg = Nothing 
Next 

oTS.WriteLine "Finished : " & Now() 

' close and release all objects created 
oTS.Close 
Set oQry = Nothing 
Set oDb = Nothing 
Set oSrvr = Nothing 
Set oTS = Nothing 
Set oFS = Nothing

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply