Looping problem in DTS with ActiveX

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

Import/Export (DTS) and Replication (2000)

Looping problem in DTS with ActiveX

Author

Topic

Hayduke
Starting Member

1 Post

Posted - 2007-04-16 : 18:36:20

In MSSQL 2000, I have a DTS package that downloads files via FTP, then processes each file. A few tasks are done when each file is processed:
(a) a holding table is truncated and 1 blank record is inserted into the holding table,
(b) the XML data in the file is inserted into the holding table via TextCopy.exe,
(c) the XML data is parsed using OPENXML and inserted into 2 additional holding tables, and
(d) the XML file is archived to another directory.
After (a), (b), (c), and (d) are completed, the DTS package loops back and executes (a), (b), (c), and (d) for the other remaining files.

It all worked flawlessly in testing, until I commented out a MsgBox line in the ActiveX task for item (b) above. Without the MsgBox command, the other tasks (b) and (c) don't appear to execute, though I can see that the looping is working, since the source files get moved to the archive location (in step (d)).

Here's a screenshot of the DTS package (it can also be viewed at http://www.nmwildlife.org/images/DTS_screenshot.gif):
[img align="right" border="0" src="http://www.nmwildlife.org/images/DTS_screenshot.gif" hspace=8 vspace=8 alt="DTS screenshot"][/img]

I think that the MsgBox issue is a red herring, in that my thought is that when I click the OK button on the MsgBox, there might be something about the return code which allows the tasks to be executed properly. However, I'm not a VBScript expert, so can't figure out where the problem lies or how to fix it.

Here's the code for the "Import w/ShellCmd" ActiveX task:

Function Main()
 Dim objShell 
 Dim strPath
 Dim strCmd

 strPath = CSTR(DTSGlobalVariables("gv_FileFullName").Value)

 strCmd = """C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TextCopy.exe"" /S ""GROVER"" /U sa /P """" /D TESTProlaw /T dc_XML /C myXML /F " & _
  strPath & _
  " /W ""WHERE 1=1"" /I /Z"

 Set objShell = CreateObject("WScript.Shell")
 objShell.Run strCmd
 Set objShell = nothing 

MsgBox ""

 Main = DTSTaskExecResult_Success
End Function

And here's the code for the "Begin Loop" ActiveX task:

Option Explicit

Function Main()

 dim pkg
 dim stpEnterLoop
 dim stpFinished

 set pkg = DTSGlobalVariables.Parent
 set stpEnterLoop = pkg.Steps("DTSStep_DTSExecuteSQLTask_2")   'Start loop at the "Truncate dc_XML" task
 set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")

 ' We want to continue with the loop only of there are more than 1 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
  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


 set pkg = DTSGlobalVariables.Parent
 set fso = CREATEOBJECT("Scripting.FileSystemObject")
 
 set fold = fso.GetFolder(DTSGlobalVariables("gv_FileLocation").Value)

 counter = fold.files.count

 'So long as there is more than 1 file carry on

 if  counter >= 1  then

 for each fil in fold.Files
  DTSGlobalVariables("gv_FileFullName").Value = fil.path
  ShouldILoop = CBool(True)
 Next

 else
  ShouldILoop = CBool(False)
 End if

End Function

The goal is to get the DTS package to run without having to manually click OK on the MsgBox; that way, I can schedule it to run automatically.

Any help would be greatly appreciated. Thanks in advance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources