Error on VB SQL ActiveX Script - PLEASE HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error on VB SQL ActiveX Script - PLEASE HELP

Author

Topic

arunanand
Starting Member

2 Posts

Posted - 2009-03-27 : 10:46:20

Hello Guys,
I have created a DTS for importing multiple text files from a folder location and carry the Transformation to load the data onto the Table. Before the Transformation i am adding a Active X Script Task to Loop for all the Text Files. But I am stuck at a VB error (not great in debugging VB) it gives me an error "Invalid procedure call or argument". The code goes like this:
' 246 (Begin Loop)
Option Explicit

Function Main()

Dim pkg
Dim conTextFile
Dim stpEnterLoop
Dim stpFinished

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)")

' We want to continue with the loop only if there is
' one or more text file in the directory. If the function ShouldILoop
' returns true then we disable the step that takes us out of the package
' and continue processing

if ShouldILoop = True then
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
conTextFile.DataSource = DTSGlobalVariables("\a-13\BGSMapps\LIVE\CLO\Databases\TEMP\RedirectionReportsRawData\AwayReports\tmpFileImport\EDF\ReDirect1.txt").Value
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if

Main = DTSTaskExecResult_Success
End Function

Function ShouldILoop

Dim fso
Dim fil
Dim fold
Dim pkg
Dim counter
Dim GetFolder

set pkg = DTSGlobalVariables.Parent
set fso = CREATEOBJECT("Scripting.FileSystemObject")
'This is where the error is appearing
set fold = fso.GetFolder(DTSGlobalVariables("\a-13\BGSMapps\LIVE\CLO\Databases\TEMP\RedirectionReportsRawData\AwayReports\tmpFileImport\EDF\Redirect1.txt").Value)

counter = fold.files.count

' So long as there are text files in the directory carry on

if counter >= 1 then

for each fil in fold.Files
DTSGlobalVariables("\a-13\BGSMapps\LIVE\CLO\Databases\TEMP\RedirectionReportsRawData\AwayReports\tmpFileImport\EDF\ReDirect1.txt").Value = fil.path
ShouldILoop = CBool(True)
Next

else
ShouldILoop = CBool(False)
End if

End Function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources