DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 1 of 1

Thread: Looping problem in DTS with ActiveX

  1. #1
    Join Date
    Apr 2007

    Looping problem in DTS with ActiveX

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

    Attached is a screenshot of the DTS package (it can also be viewed at

    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
      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)
      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!
    Attached Images Attached Images

Similar Threads

  1. DTS HELP!
    By W.G. Ryan in forum Database
    Replies: 4
    Last Post: 10-19-2002, 01:46 AM
  2. Replies: 1
    Last Post: 02-05-2002, 01:53 AM
  3. Replies: 0
    Last Post: 12-05-2001, 03:16 AM
  4. problem with imageman activex control
    By sreedhar in forum ASP.NET
    Replies: 0
    Last Post: 02-01-2001, 11:26 AM
  5. problem with imageman activex control
    By sreedhar in forum ASP.NET
    Replies: 0
    Last Post: 02-01-2001, 11:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center