How to overwrite DTS package logs everytime the package executes?
Last updated: July 13th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

Click Here to Read SQL Server DTS Best Practices

NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email with 'subscribe' in the subject line
See also:  
Database coding conventions SQL Server interview questions
Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools
SQL Server security best practices SQL Server administration best practices
Related books:
Professional SQL Server 2000 DTS Microsoft SQL Server 2000 Data Transformation Services (DTS) SQL Server DTS

How to overwrite DTS package logs everytime the package executes?

You can configure your DTS packages to log the execution status and errors to SQL Server tables or to text files. This is a very useful feature for diagnosing and troubleshooting problems and monitoring execution status of DTS packages (scheduled or otherwise).

Unfortunately there is no option exposed by DTS user interfae or object model, to clear these logs. Information always gets appended to the log files or log tables. This results in huge log tables or text files over a period of time (also depends on the package schedule) and makes it difficult to access the information.

When the packages are configured to log errors to SQL Server tables, you could configure a job to clear out these tables periodically, or you could add an "Execute SQL" task to your DTS package to clear these log tables.

It gets a little trickier if you configure your packages to log errors to a text file. You could clear these log files in several ways and this article shows you one of those.

Idea is to use the "ActiveX Script" task to instantiate the FileSystemObject object and delete the log file, so that the package creates a new log file upon every execution, effectively leaving information about the last execution only. You could obtain the name of the log file, dynamically from the package object using the LogFileName property, for example: DTSGlobalVariables.Parent.LogFileName

I myself faced this problem, and my requirements were a little different. I have dozens of DTS packages and I call most of them parallelly (these are independant packages, so I gain performance) from a master DTS package using the "Execute Package" task. I didn't want to add the same piece of code to all my packages. So, I created a general purpose DTS package called "Delete DTS Log Files" with just one "ActiveX Script" task, with the following code (VBScript):

Function Main()
	Dim FSO, FileName, Pos, CurrFileName
	Set FSO = CreateObject("Scripting.FileSystemObject")
	If Trim(DTSGlobalVariables("FileName").Value) <> "" Then
		FileName = Trim(DTSGlobalVariables("FileName").Value) + ","
	End If
 	Pos = InStr(1, FileName, ",")
 	If Replace(FileName, ",", "") <> "" Then
		Do While Pos > 0
			CurrFileName = Trim(Left(FileName, Pos - 1))
			If Trim(CurrFileName) <> "" Then 
				If FSO.FileExists(CurrFileName) Then
					FSO.DeleteFile CurrFileName, True
				End If
  			End If
			FileName = Right(FileName, Len(FileName) - Pos)
			Pos = InStr(1, FileName, ",")
	End If
	Set FSO = Nothing
	Main = DTSTaskExecResult_Success
End Function

The above VB script expects a comma separated list of file names (along with full path) in the form of a global variable named "FileName". I know the names of the log files for all my packages before hand. So, I added an additional "Execute Package" task to my master DTS package to invoke the "Delete DTS Log Files" package I just created. I declared a global variable named "FileName" in this "Execute Package" task, and specified the value as a list of comma separated log file names. I configured the master package workflow so that the "Delete DTS Log Files" package gets executed first. This results in the log files being deleted first, then all the child packages run and create new log files.