Problem with looping - data files loading from a specific directory

Question

Problem with looping - data files loading from a specific directory

Mohan Mariyappa

Old Hand

Points: 357
More actions
November 12, 2006 at 6:32 pm

#71047

Hi group,
I have designed DTS package which will load all data files kept on a specific directory. First data file load is perfect but second file, I get "Error opening datafile: The system cannot find the file specified". In fact in the ActiveX script, I am displaying full path and it is picking up the correct file name and its path. Following is the ActiveX I am using. Any information is of great help to me. Thanks in advance.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim fso, folder, files, file, filecount, filename, oPkg, stpEnd, stpContinue, stpContinue1

set fso = CreateObject("Scripting.FileSystemObject")
set folder = fso.GetFolder(DTSGlobalVariables("datafilefolder").Value)
set files = folder.Files

set oPkg = DTSGlobalVariables.Parent
set stpContinue1 = oPkg.Steps("DTSStep_DTSActiveScriptTask_4")
set stpContinue = oPkg.Steps("DTSStep_DTSActiveScriptTask_5")
set stpEnd = oPkg.Steps("DTSStep_DTSActiveScriptTask_7")

filecount = files.Count
if filecount = 0 then
  stpEnd.DisableStep = False
  stpContinue.DisableStep = True
  stpContinue1.DisableStep = True
  Main = DTSTaskExecResult_Success
  exit function
else
  stpEnd.DisableStep = True
  stpContinue.DisableStep = False
  stpContinue1.DisableStep = False
end if
for each file in files
  filename = file.name
  exit for
next
DTSGlobalVariables("filename") = filename
DTSGlobalVariables("fileCounter").Value = filecount
filepath = DTSGlobalVariables("datafilefolder").Value & DTSGlobalVariables("filename").Value
Set oConn = DTSGlobalVariables.Parent.Connections("txtSource")
oConn.DataSource = filepath
msgbox(oConn.DataSource)
Main = DTSTaskExecResult_Success
End Function

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Murphree-376363 Newbie Points: 3 More actions · Answer 1

I am not sure exactly what all your script is doing, but just glancing it looks like your for loop at the bottom is incorrect (what is the purpose, it looks like you are exiting the for after assigning the first file name).

for each file in files

filename = file.name

' move the next to the bottom??

' exit for

' next

DTSGlobalVariables("filename") = filename

DTSGlobalVariables("fileCounter").Value = filecount

filepath = DTSGlobalVariables("datafilefolder").Value & DTSGlobalVariables("filename").Value

Set oConn = DTSGlobalVariables.Parent.Connections("txtSource")

oConn.DataSource = filepath

msgbox(oConn.DataSource)

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 2

Mark, I beleive this is just one step within the DTS package. What Mohan is attempting to do is to grab the "next" file in the directory.

Mohan is this your process? Grab first file, perform operations on said file, delete and/or archive file, grab next file and repeat?

ANd it's on the "Grab next file and repeat" that the error occurs?

Have you walked the DTS package executing each step individually? This is generally what I do to debug the problem Your code looks ok. The only thing that might be wrong is this "DTSGlobalVariables("filename") = filename" should probably be this "DTSGlobalVariables("filename").Value = filename"

Mohan Mariyappa Old Hand Points: 357 More actions · Answer 3

In fact I figured what the mistake soon after I posted this.

Yes, what Cliff is saying right, I am trying to grad first file and moving to archieve folder after data loading.

Thanks a lot you input.

Brian Fricke Newbie Points: 2 More actions · Answer 4

I am having the same problem, when I dynamically swap out the Datasource on my import file,it can't find it on the loopback. The archive works fine, so when you run the package the next time it pics up the file that it could not find on the previous run. (of course then it fails again on the next). It is almost if if the datasorce change has not yet taken effect.

Brian Fricke Newbie Points: 2 More actions · Answer 5

Found another post that solved the problem by setting the "Close Connection on completion" Workflow property on the X-form