Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using FileSystemObject with DTS

By Brian Knight,

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.

Dim oFS
Dim colFile
Dim oFolder
Dim oFile

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

Dialog=Msgbox(oFile.Name)
End If
Next

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 VBScript Programmer's Reference. It gives you a background in the fundamentals and gets into the details of how to program in VBScript.

Total article views: 8129 | Views in the last 30 days: 9
 
Related Articles
FORUM

global variables

global variables

FORUM

Global variable

Assign value to global variable

FORUM

Pass inner global variable from the parent pkg

How to pass inner global variable from the parent pkg using Active Script or Dynamic Properties?

FORUM

declaring global variables

declaring global variables

ARTICLE

Using DTS Global Variables

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

Tags
dts    
miscellaneous    
programming    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones