Exporting to text - checking if file exists first

[mkell1]

I have a DTS package that I am creating that will be running every 10 minutes to dump data into a file that will then be picked up by another application. This other application needs to look for the exact same file name each time it does its import. Once the other application accesses this data, it will delete it from the file. After this point (when the text file is empty), it is safe for me to push the next batch of records into the file for the next run. Is there a way that I can access the text file and check to see if there is data in it so that I can determine if it is safe to push new data into the file yet or if I need to wait until the next run? I can't depend on the other system to be timely in it's processing of the data and clearing the file because it is very possible for the service associated with the application to stop running at times and I don't want to write over the data that did not get picked up.

I am assuming i will need some type of Active X script task in the DTS package to accomplish this, but I am clueless as to what type of syntax might let me access the contents of a text file to see if it there is data in it.

Any help anyone can provide would be greatly appreciated!

(Alternately, I may be able to have the other service completely delete the text file - how might I write an Active X script to first see if the actual file exists in the first place?)

Melissa

 

[mkell1]

I was actually able to get the Active X script to do this working. I am both checking for file existence and if the file exists, checking to see if the file is empty with the following code:

Function Main()
Dim objFSO
Dim cFilePath
Dim cFileName
Dim cFileData
Dim cFile

Const ForReading = 1

cFilePath = "D:\Melissa\"
cFileName = "UnibolSO.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

With objFSO
If .FileExists(cFilePath & cFileName) Then
'Check to see if there is any data in the text file. If the file is empty, go ahead and push data in.
Set cFile = objFSO.OpenTextFile(cFilePath & cFileName, ForReading)
If cFile.AtEndOfStream Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
cFile.Close
ELSE
Main = DTSTaskExecResult_Success
End If
End With
Set objFSO = Nothing
End Function

 

[nuVBer]

Give yourself a star...good post.