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

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:

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, ",")
		Loop
	End If
 
	Set FSO = Nothing
 
	Main = DTSTaskExecResult_Success
 
End Function