Hi,
Im expecting 6-10 files per day
I need to make an active x script in DTS to loop a directory and check
if a file exits and process each file that it finds.
If it finds a file it copys it and renames it to a generic filename
and then exits the loop so the rest of the DTS package can run on
success
ie

'Run the rest of the package so jump out of loop
Main = DTSTaskExecResult_Success
Exit For

Trouble is it can only process on file per time
Is this the most efficent way to do this and set the dts package on a
schedule
checking for a file every half hour


Function Main()


Dim fso, PreLoadFolder, LoadFolder, File, sFile, filetogo, bThere
Set fso = CreateObject("Scripting.FileSystemObject")
Set LoadFolder = fso.GetFolder(dtsglobalvariables("LoadFolder"))
Set PreLoadFolder = fso.GetFolder(dtsglobalvariables("Preload"))

'Check to see if file is ready for loading already
bThere = False
For Each File In LoadFolder.Files
If File.Name = "blcltp.txt" Then bThere = True
Next



'An error means the file does not exist and we have to go and get
one
If Not bThere Then


For Each File In PreLoadFolder.Files
sFile = File.Name
'File name examples abcltp03,abcltp22 etc etc
If InStr(LCase(sFile), "abcltp") > 0 Then
'Msgbox(dtsglobalvariables("LoadFolder") & "\" &
sFile)
fso.MoveFile dtsglobalvariables("Preload") & "\" &
sFile, dtsglobalvariables("LoadFolder") & "\" & sFile
fso.CopyFile dtsglobalvariables("LoadFolder") & "\" &
sFile, dtsglobalvariables("LoadFolder") & "\abcltp.txt"
Main = DTSTaskExecResult_Success
'Run the rest of the package so jump out of loop
Exit For
End If
Next

Else
'MsgBox ("File already there")
End If

End Function

thanks in advance
Dave

Have you seen this article

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"David Bulog" <d2ba (AT) xtra (DOT) co.nz> wrote

Thanks Allan,
Looks like a very nice sample you could use for base code for an ETL.
Will reserach and test
One question from the sample
"Here we begin to do our loop. We loop through the files in the folder
and pass the name of the file to the text file connection
This is the pump into a SQL Server table"

Question:
Modify sample: remove texr file and SQL Server connection and replace
with run DTS package task--so we can abstract our logic
Instead of passing the name of the file to the text file connection as
shown--I want to pass it to my DTS subpackage via the global
Is this possible

cheers David

Thanks Allan,
Looks like a very nice sample you could use for base code for an ETL.
Will reserach and test
One question from the sample
"Here we begin to do our loop. We loop through the files in the folder
and pass the name of the file to the text file connection
This is the pump into a SQL Server table"

Question:
Modify sample: remove texr file and SQL Server connection and replace
with run DTS package task--so we can abstract our logic
Instead of passing the name of the file to the text file connection as
shown--I want to pass it to my DTS subpackage via the global
Is this possible

cheers David

Yep

What goes in the middle of the loop is your business. I have used a number
of combinations.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"David Bulog" <d2ba (AT) xtra (DOT) co.nz> wrote