I need an example or some ideas - how to do this -

I need to run a job that searches a directory every hour to see if any files
are available for upload - and then upload it into a SQL data base - and
then move the file to another directory.

I know how to manaully upload the file using DTS - and move the file to
another location - i don't know how to search a directory and if there is a
file there - to upload it.

Thanks

Dave

Hi Dave,

Do you know the name of the file? If you do, you can use the
Scripting.FileSystemObject's FileExists method to check whether the file
exists or not. If so, you make the task successful, if not you make it fail.
Something like:

Function Main()
....
set oFSO = CreateObject("Scripting.FileSystemObject")
if oFSO.FileExists("c:\Myfolder\MyFile.txt") then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End function

You then use Success precedent constraint to go to the next task which
imports the file. The import only happens when the file is found. You then
schedule the package to run every hour.

If you don't know the name of the file, you can use the Folder object's
Files property to get a collection of the files in the folder. You then loop
around the files, each time setting the DataSource property of the Text
Source connection to the file name and importing the file.

Here is a code snippet:
option explicit
Function Main()

Dim oFS, colFile, oFolder, oFile, position, oPkg, cFile, con
Set oPkg = DTSGlobalVariables.Parent
set con = oPkg.Connections("SourceFile")
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFS.GetFolder(DTSGlobalVariables("gvFolder").Value)
set colFile = oFolder.Files
position = 0
for each cFile in colFile
position = position + 1
if position = cint(DTSGlobalVariables("gvFileCurrent").value) then
set oFile = cFile
con.DataSource = DTSGlobalVariables("gvFolder").Value & "\" & oFile.Name
end if
next
if position > DTSGlobalVariables("gvFileCurrent").value then
Main = DTSTaskExecResult_Success
exit function
end if

Main = DTSTaskExecResult_Success
End Function

Hope this helps.

Charles Kangai, MCT, MCDBA

Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services"
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"






[quoted text, click to view]