SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

DTS and Global Variables

By Alex Kersha,


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.


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).


  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)


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"
		 /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.


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

   ‘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
			If swap_Pos <> i Then
	         Set arrFileList (swap_pos) = startingKey
				Set arrFileList(i) = mostRecent
			End If

       	Set startingKey = Nothing
			Set mostRecent = Nothing

		'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)
	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("Path = " & varFolderPath)
	objTxtFile.WriteLine("Mask = " & varFileMask)
	objTxtFile.WriteLine("Files to keep = " & varNumber)
	objTxtFile.WriteLine(" ")


	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(" ")


	Set objFile = Nothing
	Set objTxtFile = Nothing

End Sub
Total article views: 19678 | Views in the last 30 days: 31
Related Articles

global variables

global variables


declaring global variables

declaring global variables


Using DTS Global Variables

Global variables are a little known solution in Data Transformation Services (DTS). Often packages ...


Global variable

Assign value to global variable


Global Variables

Assign MAX(date) to Globale variables

sql server 7