Last week we visited how to use global variables in DTS.
This week we'll read a file from a directory and create a dialog box from the file's name.
This exercise is especially useful when you're reading a file from a directory to dyanmically set properties of a task or connection.
First create a directory on your C drive called data. Place two empty text files in those directories called data1.txt and data2.txt.
Next, create two global variables as shown below. You can create a global variable by clicking on Properties in the Package menu.
Now that your global variables are created, you can begin your ActiveX script task. Click on the ActiveX script tasks under the tasks menu.
Below is the script in its entirety.
Let's go ahead and take this script apart. This first step is to declare your variables and create your objets.
Since we are using the FileSystemObject, we will create the object and use the variable
oFS to represent the object.
Set oFS = CreateObject("Scripting.FileSystemObject")
Since we are not making any changes to the package or its global variables, we will
not need the following line. However, I include this line for future expansion of the script:
Set oPkg = DTSGlobalVariables.Parent
We will now need a few variables to be set before we can continue. We will use
the global variables that you declared earlier in this article. This is the main benefit of using global variables.
If you had 25 scripts all running in conjuction, without the use of global variables, you would have to change 25 scripts
for a minor change such as moving the location of the files. With the use of global variables, you can just set the global variable in one
location and allow it to be called from multiple scripts. A big time saver!
The GetFolder method will set which folder we will look in to retrieve the files.
Now that you have the folder set, the oFolder.Files method will list all the files in that folder.
Set oFolder = oFS.GetFolder(DTSGlobalVariables("gsUploadDirectory").value)
Set colFile = oFolder.Files
You need to now loop through all the files in that directory that end with the extention .TXT and
perform an action on them. That is done through a For..Each loop. In the below code
we are looping through our files in the colFile array and loading a dialog box with the file name when found.
For Each oFile in colFile
If UCase(Right(oFile, 4)) = DTSGlobalVariables("gsExtention").value Then
Go ahead and execute the package. If all goes well, you should see the following:
Next week we will go into setting connections dynamically.
A great book if you are interested in further researching VBScript is
It gives you a background in the fundamentals and gets into the details of how to program in VBScript.