Hello -

I'm trying to load all the files in a folder. I found code on the web
and modified it somewhat. My problem is that only the last file in
the folder gets loaded. It loops through and finds all the names, but
doesn't kick off the data pump task until it's done looping through
the file names.

The data pump task has a workflow property of the activex step being
successful.

I'm running SQL server 2000. Here's the activex code.

Thanks - I hope it's something simple....

---------------------------
Option Explicit

Function Main()

dim pkg
dim conTextFile
dim stpEnterLoop
dim stpFinished
dim fso
dim fil
dim fold
dim counter
dim counter2

set pkg = DTSGlobalVariables.Parent
set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
set conTextFile = pkg.Connections("Text File (Source)")
set fso = CREATEOBJECT("Scripting.FileSystemObject")
set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation" ).Value)

counter = fold.files.count

counter2 = 0
for each fil in fold.Files

If counter2 = counter Then
Exit For
End If

counter2 = counter2 + 1
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
DTSGlobalVariables("gv_FileFullName").Value = fil.path
conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
Next
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting

Main = DTSTaskExecResult_Success
End Function
--------------------------

You look to have modified my code somewhat.

Why does what I wrote not fit the bill?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"dutchie" <lenine2 (AT) attbi (DOT) com> wrote

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

worked. I'm new to DTS in general. It was hard for me to figure out
which thing did what. And I STILL can't figure out why the next step
won't run until it's looped through all of the file names.

I apologize if I pulled a faux pas. But -- I still can't get it to
work!

dutchie

It looks as though where you iterate over the files is not exiting for every
file.

Basically I do this

Look in the directory
Do we have files?
Yes then set the GV to the name of the first file we pick up and exit the
loop.
Assign the name of the file to the relevant properties
Pump the data
Archive the file we just picked up
Return to start
Pick up the first file.......................




--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"dutchie" <lenine2 (AT) attbi (DOT) com> wrote