I have been working on a DTS job to FTP a text file from another site,
then do some name manipulation and run it thru my normal process for
this file (we're changing how we pick up this source file).

The filename will be always be named:

YYYYMMDD.TXT

and it'll be the only one out there on the ftp site, and the folder
there will be empty until there's a new one of them.

When I read in the file, I need to store it's name in a global variable,
and then write out this global variable into a table on my database.
All this I've gotten done.

What's next is to do a lookup on the table, to make sure this value
isn't already in there. I haven't been able to figure out what I need
to do to look for it there. I'll need to do this when I grab the
filename, to determine where I need to go with it.

The code I have where it's got to be done is here:

--- Start
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()

Dim fso, folder, file, filecollection
Dim oFSO, sFileName

Set fso=CreateObject("Scripting.FileSystemObject")
Set folder=fso.GetFolder("\\myserver\mydatafolder\myin putfolder")

Set filecollection=folder.Files

For each file in filecollection

DTSGlobalVariables("ImportFileName").Value=file.Na me
Exit For

Next

DTSGlobalVariables("ImportFilePathName").Value="\\ myserver\mydatafolder\myinputfolder\"
& DTSGlobalVariables("ImportFileName").Value



' Get the name of the file from the global variable
"ImportFileName"
sFilename = DTSGlobalVariables("ImportFilePathName").Value

Set oFSO = CreateObject("Scripting.FileSystemObject")

'msgbox sFilename

' Check for file and return appropriate result
If oFSO.FileExists(sFilename) Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If

Set oFSO = Nothing

End Function
--- end

If it's there, I want to skip out of the job with no further activity,
and proceed to the next step in my batch job. If it's not there, I need
to process this as an updated file, writing the filename (YYYYMMDD) out
to my table.

Later on in another batch job step, I'll need to read the newest record
on that table in order to put in that date as the "As Of" date on the
table.

Secondly, in the FTP step, I want to skip out of the job if the source
folder on the other system is empty.


Any ideas as to how to do this lookup within the activex?

Thanks

BC

You mention that you want to do a lookup in a table but the code is working
with the FileSystemObject. You mention you have the code done that
populates a GV with the name of the file and that now you want to check in
the table that the filename is not already there.

I would use an ExecuteSQL task and I would do it something like this

SELECT COUNT(*) as Amount FROM TABLE WHERE Column = ?

I would map Amount to an output global variable and ? would be the name of
the file you are trying to find in the table.

In an Active Script task you can then check to see that the global variable
populated by Amount is > 0



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Blasting Cap" <goober (AT) christian (DOT) net> wrote