Summary

I've seen several different articles devoted to using global

variables within DTS packages throughout the various SQL Server communities and

knowledge bases. What I've never seen is a complete step by step implementation

using SQL job, DTS package and some worthwhile utility tied to the process.

Here I’ll try show you how I’ve implemented a solution to purge files from my

production environment based on a certain number of backup files in a target

folder.

Background

The issue many DBAs run into is how to control the amount of

data preserved in the local file system of a production server. At PeopleBusinessNetwork we have a process that fits each

client’s data load and open maintenance windows. The most difficult part of the

process is figuring out just what type of scenario to use. Do you purge backups

based on date in relation to the current day or by the number of the files you

currently have in a folder, choosing to keep the most recent X files?

If the scheduling of your data loads is consistent, say

every day at 8:00pm, then you’re in luck and you can go by date deleting the

files say after 14 days or whatever is best for the client, you and the space

on your server.   If however you skip

weekends or the loads only happen every few days, or even once a month then

your purge by date is no longer valid. To resolve this we’ve decided to come up

with a scheme to purge files based on the number of backups that exist in a

certain folder. There are various utilities available on the web for this

purpose such as ForFiles.exe that require install and DLL

registration. I specifically wanted to avoid this as well as installing the

.Net framework on a production server running SQL 2000 to enable some .Net tool

for the job. Keeping the simple things simple, it’s pure SQL 2000 job, DTS

package and some good ole Vbscript ( I know, this is an arguable point).

Requirements

1. SQL job to call DTS package
2. DTS package with global variables defined
3. ActiveX script task to purge files based on number existing in target folder(s)

Implementation

I started with the DTS package and set it up with the

thought that it would be called by a scheduled SQL job and that I would want to

be able to reuse the same package for every type of file in a target folder.

Also, I wanted to be able to update the number of files to keep variable

depending on the client it was used for.

The DTS package itself is super simple. It has only 1

ActiveX script task defined in it. No need for connections, Dynamic properties

tasks or anything else. We’ve taken care of everything with the VBScript

itself. We have a policy of documenting our DTS packages within the package

itself using the annotation tool. Here’s a view of the package:

Step by Step (DTS)

1. Open Enterprise Manager
2. Expand the SQL Server Group, local server, Data Transformation Services node
3. Right click on [Local Packages] and choose the New Package menu item.
4. Drag the ActiveX script task icon to the white space of your package (see

   above)

5. Enter a short description in the field at the top of the scripting window
6. Write your script (I’ve included my listing at the end of this article)
7. Save your package as type SQL Server or a Structured Storage File depending on how

   you intend to access the package later. For the purpose of this article,

   we’ll be saving as SQL Server type which makes an entry into the

   sysdtspackages system table (among others).

Preparing for Global Variables

Now that you have a package saved on your server, we need to

make sure that it’s ready to accept the global variables that your SQL job will

be passing to it.

1. With the package still open, right click on the white space and choose [Package Properties]
2. Click on the [Global Variables] tab
3. Enter the name of each of the global variables that will be passed by your SQL job. My suggestion is to check off “Explicit Global Variables”, which forces you to declare each of the variables that will be used. This works very much the same way as the Option Explicit syntax in VBScript.
4. Be careful to specify what each of the variable types are. DO NOT depend on any conversion to take place here.
5. If you’re using integers, there must be an integer value placed in the [Value] column. We use 0 for a default.
6. Click [OK] and save and close the package

The SQL job

Now we’re ready to create a job to call the package we

created and pass to it the variables we need to purge the files.

1. Open the Management, SQL Server Agent node
2. Right click on [Jobs] and choose New Job
3. Define a short, meaningful name for your job
4. Fill out a short description and choose a category for your job. “Database

   Maintenance” might fit this specific example.

5. Be careful with the owner drop down. For purposes of this example use a local

   or Domain admin account with rights to the databases or just use “sa”

   which I would normally tell you is not best practice unless you ABSOLUTELY

   need to. Security and login rights are beyond the scope of this document.

6. An example of how I filled out the above information is below:

1. Now click on the [Steps] tab
2. Choose [New Step] from the buttons at bottom
3. Fill in a meaningful name for your step
4. Choose [Operating System Command] from the [TYPE] drop down
5. Fill in the command to call your package
6. An example of the step window and the command I’m using is below with an

   explanation of it’s syntax.

7. Click [OK] and then [OK] again to save and close your job. I won’t go into the [Advanced]

   tab as it is beyond the scope of this article and unnecessary for our

   example.

Example Command:

DTSRUN /SServerName /N"Purge Files by Number -DTS" /AgvFileMask:8=".bak"
       /AgvFolderPath:8="C:\temp" 
 /AgvLogPath:8="C:\Temp\DTSPurgeLog_Number.log" /AgvNumber:3=4 /E

The Job Step Syntax

The switches:

1. /S = Server name, can be WINS name or IP Address
2. /N = Package name, put it in double quotes if there are any special chars or

   spaces

3. /A = global variable, you’ll see all the variables we defined in this statement
4. the “:8” denotes a string being passed
5. the “:3” denotes an integer
6. /E = trusted login, this can be replaced with /U and /P for username and Password if you prefer. We have a policy of not putting any security or login information out in plain view.

As you may have already guessed, this command could be easily run from a command line. DTSRUN is an executable available to a “COMMAND” window and will also show the output of the DTS package including any errors. This is very cool and a quick way to keep running a package without needing to open an instance of Enterprise Manager or Query Analyzer.

Getting it Done

Ok, so now we have a DTS package all setup and global

variables defined. We also have a SQL job defined passing our globals along.

Now we just need a VBScript to fill in the task we dragged to the DTS Designer

window. I’ve included the full listing below complete with comments. In a

nutshell, the script does the following:

1. Gets the globals from the SQL job
2. Opens the target folder (gvFolderPath)
3. Creates a collection of all the files there
4. Finds each of the “.bak” files and fills an array with the file objects

   (gvFileMask)

5. Using a bubble sort, reorders the files most recent on top
6. Deletes all the files in the array (and therefore the folder) except the most

   recent X number of files (in this case 4). (gvNumber)

7. Logs the variables passed and the files deleted to a text file on the server.

   (gvLogPath)

This process has been tested over several iterations and works beautifully. I would highly recommend testing on a Development server before letting this loose in Production. The entire process takes about 20 minutes plus testing time to complete. My speciality is not VBScript but I can get things done so if you find a better way to do this, please let us all know. From my experience writing this, I would strongly encourage you to use the

Option Explicit syntax at the top of your script to force you to declare all variables. Though a pain at first, it may help save you hours of mindless DEBUG time finding a rogue variable being used or other such errors.

Conclusion

DTS is a very powerful tool in SQL Server. If used to its fullest extent it can easily take the place of some of the more mundane tasks now running under the auspices of windows based scripts on your servers. Using global variables DTS can be extended to do a variety of tasks flexibly and efficiently.

**Special thanks to Paul Downing, Engineer, PeopleBusinessNetwork for his help with the bubble sort

in the VBScript used.

Cheers

VBSCRIPT Listing

'**********************************************************************
Purge files script
'**********************************************************************
‘Variables declared outside function call so they can be used in each function
Dim varLogPath
Dim varFolderPath
Dim varFileMask
Dim varNumber
Dim varFileName
Dim arrFileList()
Function Main()
Dim objFSO
Dim objFolder
Dim objFileCollection
Dim strSourceFile
Dim varToday
Dim intFileCount
Dim intX
‘Get the global variables passed by the SQL job
varNumber = DTSGlobalVariables("gvNumber").Value
   varFilemask = DTSGlobalVariables("gvFileMask").Value
varFolderPath = DTSGlobalVariables("gvFolderPath").Value
varToday = Date
'Write variables out to debug log
   Call DebugLog
   ‘Open a FileSystemObject and fill it with files from the target folder
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(varFolderPath)
   Set objFileCollection = objFolder.Files
   intFileCount = objFileCollection.Count    
   ReDim arrFileList(intFileCount)
   intX = 0
   'Fill the array with the files matching the gvFileMask global variable
   For Each objFile in objFileCollection
      If inStr(1, objFile.name, varFileMask, 1) > 0 Then
         arrFileList(intX) = objFile = intX + 1
      End If
   Next
   ‘make sure the array size doesn’t change and values aren’t lost
   ReDim Preserve arrFileList(intX-1) 
   intFileCount = UBOUND(arrFileList)
   'If there are more than X files in the folder
   If intFileCount > varNumber Then
      'Sort the array using Bubble Sort
      Dim startingKey
Dim mostRecent
Dim swapPos
Dim ValueToCompare
'Loop thru array and move most recent files to top
For i = 0 To UBOUND(arrFileList) - 1
'usefirst element as start and most recent value
set startingKey = arrFileList(i)
set mostRecent = arrFileList(i)
swap_Pos = i
for k = i + 1 To UBOUND(arrFileList) 
'if most Recent date is earlier the next in array, swap them
if mostRecent.DateLastModified < arrFileList(k).DateLastModified then
             swap_pos = k
Set mostRecent = arrFileList(k)
End If
next
If swap_Pos <> i Then
         Set arrFileList (swap_pos) = startingKey
Set arrFileList(i) = mostRecent
End If
       Set startingKey = Nothing
Set mostRecent = Nothing
Next
'Delete the files from the folder and log their names
For m = 4 To UBOUND(arrFileList)
'Call LogFileName(arrFileList(m).name & " " & cStr(arrFileList(m).DateLastModified))
     'objFSO.DeleteFile arrFileList(m)
Next
End If
'more than X files in folder
' Clean Up
   Set objFile = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
Set objFileCollection = Nothing
'Set arrFileList = Nothing
Main = DTSTaskExecResult_Success
End Function
'Outputs the variables passed into the function by the SQL job step
Sub DebugLog
Dim objFile, objTxtFile
Const ForReading = 1, ForWriting = 2, ForAppending = 8
varLogPath = DTSGlobalVariables("gvLogPath").Value
Set objFile = CreateObject("Scripting.FileSystemObject")
Set objTxtFile = objFile.OpenTextFile(varLogPath, ForAppending, True)
objTxtFile.WriteLine("Logging File Purge Activity: " & Now)
objTxtFile.WriteLine("----------------------------------------")
objTxtFile.WriteLine("Path = " & varFolderPath)
objTxtFile.WriteLine("Mask = " & varFileMask)
objTxtFile.WriteLine("Files to keep = " & varNumber)
objTxtFile.WriteLine(" ")
objTxtFile.Close
Set objFile = Nothing
Set objTxtFile = Nothing
End Sub
'Outputs the name of the files in the array row by row
Sub LogFileName(fileName)
Dim objFile, objTxtFile
Const ForReading = 1, ForWriting = 2, ForAppending = 8
varLogPath = DTSGlobalVariables("gvLogPath").Value
Set objFile = CreateObject("Scripting.FileSystemObject")
Set objTxtFile = objFile.OpenTextFile(varLogPath, ForAppending, True)
objTxtFile.WriteLine(fileName & " DELETED")
objTxtFile.WriteLine(" ")
objTxtFile.Close
Set objFile = Nothing
Set objTxtFile = Nothing
End Sub