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
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).
- SQL job to call DTS package
- DTS package with global variables defined
- ActiveX script task to purge files based on number existing in target folder(s)
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)
- Open Enterprise Manager
- Expand the SQL Server Group, local server, Data Transformation Services node
- Right click on [Local Packages] and choose the New Package menu item.
- Drag the ActiveX script task icon to the white space of your package (see
- Enter a short description in the field at the top of the scripting window
- Write your script (I’ve included my listing at the end of this article)
- 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.
- With the package still open, right click on the white space and choose [Package Properties]
- Click on the [Global Variables] tab
- 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.
- Be careful to specify what each of the variable types are. DO NOT depend on any conversion to take place here.
- If you’re using integers, there must be an integer value placed in the [Value] column. We use 0 for a default.
- 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.
- Open the Management, SQL Server Agent node
- Right click on [Jobs] and choose New Job
- Define a short, meaningful name for your job
- Fill out a short description and choose a category for your job. “Database
Maintenance” might fit this specific example.
- 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.
- An example of how I filled out the above information is below:
- Now click on the [Steps] tab
- Choose [New Step] from the buttons at bottom
- Fill in a meaningful name for your step
- Choose [Operating System Command] from the [TYPE] drop down
- Fill in the command to call your package
- An example of the step window and the command I’m using is below with an
explanation of it’s syntax.
- 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
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
- /S = Server name, can be WINS name or IP Address
- /N = Package name, put it in double quotes if there are any special chars or
- /A = global variable, you’ll see all the variables we defined in this statement
- the “:8” denotes a string being passed
- the “:3” denotes an integer
- /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:
- Gets the globals from the SQL job
- Opens the target folder (gvFolderPath)
- Creates a collection of all the files there
- Finds each of the “.bak” files and fills an array with the file objects
- Using a bubble sort, reorders the files most recent on top
- Deletes all the files in the array (and therefore the folder) except the most
recent X number of files (in this case 4). (gvNumber)
- Logs the variables passed and the files deleted to a text file on the server.
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.
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.
'********************************************************************** 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