Re: DTS Workflow Question

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 07/01/04 

Date: Thu, 1 Jul 2004 11:33:14 +0100

In message <F76A784B-EDB7-4207-8E6D-7945662860D3@microsoft.com>, Kirk
<Kirk@discussions.microsoft.com> writes
>I am using SQL Server 2000 and I am writing a DTS package that loops
>through a recordset and calls another package which does some work
>based upon passed values. It works and loops great unless the called
>package fails (which it can since it is connecting to an external
>server). What I want to do is this:
>
>If the called package fails then run the Execute SQL task which writes
>the failure to a table and on completion of that then go to the ActiveX
>Script handling the loop routine.
>If the called package succeeds then go to the ActiveX Script handling
>the loop routine.
>
>What is happenning is that if it succeeds then neither workflow is
>continued (which means no loop) and it the execute package call fails
>then it stops completely.
>
>Any thoughts?

You cannot have two constraints leading to the same task, and the use
AND logic.

You could duplicate the ActiveX Script to restart the loop, and add it
again after the Exec SQL Task.

or

Use the skip task concept. ExecPkg -> ExecSQLErrorLog-> ActXLoopEnd

Using the three tasks in the order above and ensuring the first
constraint in On Complete, then you could use Workflow Script on the
Exec SQL Task to skip it when the Exec Pkg Task result was OK.

Personally I would duplicate the ActiveX Script Task as it will just be
a lot simpler to maintain. 

-- 
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Relevant Pages

  • Using LoadFromSQLServer in ActiveX script Task error
    ... I used the following code in an ActiveX Script task to run an DTS package. ... I am a new SQL Server user, I would love to see if anyone can help ... Set oPkg = CreateObject ...
    (microsoft.public.sqlserver.dts)
  • Re: which authentication mechanism should I use?
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >execute rather than just assigning its sql statement and execute it later.I need the result in my Activex Script. ... > 'EXIT from function if there is another instance of the same package is running ...
    (microsoft.public.sqlserver.dts)
  • Re: DataPumpReadfile
    ... >This is not a existing package. ... >in a package using ActiveX script, so that I can use the ... then change the existing connections and taskat run-time from within ... global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • Re: ActiveX creating Excel object
    ... >I have an ActiveX script in my DTS Package, which generates excel reports, ... global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • SQL Server Import Question
    ... their respective tables on SQL Server 2000. ... is there any way to make a loop? ... Perhaps an ActiveX script that ... browses through a list of files in a dir/table, dynamically changes the ...
    (microsoft.public.sqlserver.dts)
Loading