November 12, 2006 at 6:32 pm
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
November 13, 2006 at 8:27 am
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)
next
Main = DTSTaskExecResult_Success
November 15, 2006 at 8:40 pm
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"
November 15, 2006 at 8:48 pm
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.
December 15, 2006 at 11:51 am
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.
December 15, 2006 at 1:41 pm
Found another post that solved the problem by setting the "Close Connection on completion" Workflow property on the X-form
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply