June 17, 2005 at 2:11 pm
I'm trying to import mutiple text files with the same package using a loop. The code will process fine if only one of my files is present but as soon as it tries to process the second file I get an error that reads "error opening datafile: the system cannot find the specified file".
The Transformation task shows the correct file names in the data source (after they've been updated from the vbscript) etc but I can't get it to process the 2nd file. Any help would be appreciated.
Code:
Function Main()
Dim objPkg
Dim conTextFile
Dim stpEnterLoop
Dim stpFinished
dim objCT 'custom task
set objPkg = DTSGlobalVariables.Parent
set stpEnterLoop = objPkg.Steps("Copy Data from DAYTO to [AFD].[dbo].[DSR] Step") 'set copy data step reference
set stpFinished = objPkg.Steps("DTSStep_DTSActiveScriptTask_2") 'set finished step reference
set conTextFile = objPkg.Connections("Source Text File") 'set connection reference
set objCT = objPkg.Tasks("Copy Data from DAYTO to [AFD].[dbo].[DSR] Task").CustomTask
'Continue looping through until all DSR files have been processed
'If ContLoop returns true then disable the exit app function and continue processing
if ContLoop = True then
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
' Set new table/view value of task
conTextFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
objCT.SourceObjectName = DTSGlobalVariables("gv_FileFullName").Value
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if
set objPkg = nothing
Main = DTSTaskExecResult_Success
End Function
Function ContLoop
Dim fso
set fso = CREATEOBJECT("Scripting.FileSystemObject")
If (fso.FileExists("c:\temp\dayto.txt")) Then
DTSGlobalVariables("gv_FileFullName").Value = "c:\temp\dayto.txt"
ContLoop = CBool(True)
elseIf (fso.FileExists("c:\temp\limes.txt")) Then
DTSGlobalVariables("gv_FileFullName").Value = "c:\temp\limes.txt"
ContLoop = CBool(True)
elseIf (fso.FileExists("c:\temp\pacif.txt")) Then
DTSGlobalVariables("gv_FileFullName").Value = "c:\temp\pacif.txt"
ContLoop = CBool(True)
elseIf (fso.FileExists("c:\temp\japan.txt")) Then
DTSGlobalVariables("gv_FileFullName").Value = "c:\temp\japan.txt"
ContLoop = CBool(True)
else
ContLoop = CBool(False)
End if
End Function
June 17, 2005 at 2:24 pm
Better put 4 txtfiles as source in you DTS and 4 connection to dest and 4 transf task so you can use the pararelism offered by DTS
Vasc
June 20, 2005 at 6:07 am
I do the exact same thing but instead of looking for the existence of a file I just import all the files in the directory looping through the files use filesystemobject.
This article is really good and easy to follow. Also makes debugging and administration of your package easier:
http://www.databasejournal.com/features/mssql/article.php/1461661
June 21, 2005 at 8:23 am
Many THANKS!! The key was to "close the connection on completion" in the workflow properties. It works like a charm.
This is a great web site!! I'll recommend it to my associates!
Melissa
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply