Post reply

Controlling DTS Workflow from ActiveX Script

  • June 30, 2004 at 9:33 am

    #59714

    Hi,

    We have an ActiveX Script as the first task in a DTS Package. It archives a file, and copies it to the working directory on the server for importing and enrichment of the data.

    The following steps import (using the file as a Text File Connection) by transforming the data to the SQL Server table connection, then they enrich the data end export it to a final output file.

    The final step cleans up.

    Trouble is, if the file is not there we don't want an error raised, we just want the DTS Package to stop with no errors.

    How can we do this?

    I've tried using a workflow with 'On Error' precedence but the Package always fails and raises an error. I've also tried using an If..Then block with atest for the file and setting 'Main=DTSTaskExecResult_Failure' if the file isn't there...

    Any ideas?

    thanks

    Philip

  • July 1, 2004 at 1:51 am

    #512815

    Use a workflow script and set the next step to inactive. This will cause the remaining steps to be bypassed.

    --------------------
    Colt 45 - the original point and click interface

  • July 1, 2004 at 1:52 am

    #512816

    There are a few options:

     

    You could disable step 2 using ActiveX code (probably would be my choice).

    SET <Task2>.DisableStep=1

     

    Or you could drop the precedence constraint from the PrecedenceConstraints collection.

     

    See Disconnected Edit for the DTS Object Hierarchy to give you an idea of how to carry out these operations.

     

    Regards

    Jamie Thomson

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • July 1, 2004 at 4:54 am

    #512841

    Check out http://www.sqldts.com and look at dts loops, it describes the way the logic works and has a downloadable dts example.


    Shamless self promotion - read my blog http://sirsql.net

  • July 1, 2004 at 7:29 am

    #512883

    Right-click on the archive active-X object, select 'Workflow Properties' under 'Workflow'

    Select the Options tab

    Tick 'Use ActiveX scripts' and select 'Properties'

    Insert the following ActiveX script, and modify the filename for your needs

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

     Dim fso 'File System Object

     Set fso = CreateObject("Scripting.FileSystemObject")

     If (fso.FileExists("\\server\\filename")) Then

      Main = DTSStepScriptResult_DontExecuteTask

     Else

      Main = DTSStepScriptResult_ExecuteTask

     End If

    End Function

    If the file to be archived does not exist, then the archive step will not execute.  Any steps based on that step will also not execute.  The DTS will complete successfully, with no errors.

     

    When in doubt - test, test, test!

    Wayne

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply