Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Automate DTS Logging

By Haidong Ji,

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.

DTS Logging

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.

Total article views: 11963 | Views in the last 30 days: 3
 
Related Articles
FORUM

DTSGlobalVariables

hi, how can i to use the DTSGlobalVariables on Sql scripts? I mean, i'm using a Dts with a date vari...

FORUM

DTSGlobalVariables

hi, how can i to use the DTSGlobalVariables on Sql scripts? I mean, i'm using a Dts with a date vari...

FORUM

Execute SSIS Package from other server

Execute SSIS Package from other server

FORUM

Executing multiple SSIS packages

Executing multiple SSIS packages

FORUM

use global variables

DTSGlobalVariables

Tags
dts    
programming    
sql server 7    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones