Many DTS packages are written by developers who may not know much about SQL and/or SQL Server. With the popularity of DTS as an ETL tool increasing everyday, many SQL Server DBAs are called to debug and troubleshoot DTS packages that were poorly written and organized. One important tool to help this is DTS logging.

A DBA can use package log to troubleshoot problems that occurred during the execution of a DTS package. The DTS package log, unlike SQL Server error log and the DTS exception log, contains information about the success or failure of each step in a package and can help determine the step at which a package failure occurred. Each time a package executes, execution information is appended to the package log, which is stored in msdb tables in SQL Server or in SQL Server Meta Data Services. You can save package logs on any server running an instance of SQL Server 2000. If a package log does not exist, the log will be created when a package is run.

An executing package writes information to the package log about all steps in the package, whether or not an individual step runs. If a step runs, it will retain start and end times, and the step execution time. For steps that do not run, the log lists the steps and notes that the step was not executed.

In addition, with the proliferation of packages on a server or servers, you can use DTS logging records to determine which package(s) is no longer used and get rid of orphaned packages. I'll probably write this in a different article.

You can turn on DTS logging manually. However, it can be tedious and time-consuming, especially if you have many packages to manage. In this article, I will show you how to turn on DTS logging programmatically.

Package logging is only available on servers running an instance of SQL Server 2000. As such, this article only applies to SQL Server 2000.

Manually turn on DTS logging

One way to turn on DTS logging is to open the package in DTS designer, with no object selected within the package, click the property icon or, go to the Package menu and select Properties. The package property window (not property window of any individual component) will pop up. You can click on the Logging tab and fill out the relevant information to start DTS logging. See the attached image.

However, if you have many packages to manage, manually turning on each package logging can be tedious and time-consuming. That is why I wrote the following scripts to accomplish this task.

Use ActiveX scripts to turn on DTS package logging automatically

With DTS package automation, we naturally turn to SQL-DMO. Using SQL-DMO, you can pretty much automate anything that is SQL Server related.

The following code uses SQL-DMO to turn on package logging for a given server. You will need to create a package. Within the package, create an ActiveX task that has the attached code below. You then need to create 3 global variables (data type string) within this package: ServerName, SQLLoginID, and SQLLoginPassword. The variable names explain their purpose. After you give appropriate values to the three global variables, you are good to go.

The key concept used here is the PackageInfos collection. The EnumPackageInfos method returns a PackageInfos collection containing information about all the packages stored in SQL Server 2000. We then use PackageInfos.Next method to walk through every package within the collection and turn on the logging property of that package.

After running this task, all packages logging will be turned on. However, if you create this package on the same server with the other packages, this ActiveX package's logging property will not be turned on because it is in use. It cannot flip the logging button while it is open.

Another thing you will notice is that the visual layout of various package components will change after this is run, but the components remain the same.

'**********************************************************************
'  Author:Haidong "Alex" Ji
'  Purpose: To turn on package execution logging for each and every DTS
'packages in a given server. This is especially useful
'when there are many (hundreds) packages to handle.
'  Note:1. This script uses DTS global variables called ServerName,
'SQLLoginID and SQLLoginPassword;
'2. ServerName defines the server whose DTS packages'
'execution logging you want to change. The other 2 DTS global
'variables' names explain their purposes. Change those
'variables' values to suit your specific needs
'3. It seems that the layout of various Package component will
'change after this is run, but the components remain the same
'************************************************************************
Function Main()
Dim oApplication   ' As DTS.Application
Dim oPackageSQLServer  ' As DTS.PackageSQLServer
Dim oPackageInfos   ' As DTS.PackageInfos
Dim oPackageInfo     ' As DTS.PackageInfo
Dim oPackage           ' As DTS.Package
Set oApplication = CreateObject("DTS.Application")
Set oPackageSQLServer = oApplication.GetPackageSQLServer(DTSGlobalVariables("ServerName").Value, DTSGlobalVariables("SQLLoginID").Value, DTSGlobalVariables("SQLLoginPassword").Value, 0)
Set oPackageInfos = oPackageSQLServer.EnumPackageInfos("", True, "")
Set oPackageInfo = oPackageInfos.Next
'Note: It is IMPORTANT that oPackage be instantiated and destroyed within the loop. Otherwise,
'previous package info will be carried over and snowballed into a bigger package every time
'this loop is run. That is NOT what you want.
Do Until oPackageInfos.EOF
    Set oPackage = CreateObject("DTS.Package2")
    oPackage.LoadFromSQLServer DTSGlobalVariables("ServerName").Value, DTSGlobalVariables("SQLLoginID").Value, DTSGlobalVariables("SQLLoginPassword").Value,DTSSQLStgFlag_Default , , , , oPackageInfo.Name
    oPackage.LogToSQLServer = True
    oPackage.LogServerName = DTSGlobalVariables("ServerName").Value
    oPackage.LogServerUserName = DTSGlobalVariables("SQLLoginID").Value
    oPackage.LogServerPassword = DTSGlobalVariables("SQLLoginPassword").Value
    oPackage.LogServerFlags = 0
    oPackage.SaveToSQLServer DTSGlobalVariables("ServerName").Value, DTSGlobalVariables("SQLLoginID").Value, DTSGlobalVariables("SQLLoginPassword").Value, DTSSQLStgFlag_Default
    Set oPackage = Nothing
    Set oPackageInfo = oPackageInfos.Next
Loop
'Clean up and free resources
Set oApplication = Nothing
Set oPackageSQLServer = Nothing
Set oPackageInfos = Nothing
Set oPackageInfo = Nothing
Set oPackage = Nothing
Main = DTSTaskExecResult_Success
End Function

Conclusion

In this article, I showed you how to use SQL-DMO to turn on DTS package logging. This is especially useful when there are many (hundreds) packages to handle. For DTS package ownership and scheduling issue, please see a different article I wrote awhile ago.