Hi
I have a SQL Server 2000 server and an access front end.  I want to be
able to trigger a DTS package from Access.  I have a table that stores
a value, (idle, waiting to export, exporting).

When the user wants to trigger the package, Access puts the 'waiting to
export' value in a table.

The package is set to run every 10 minutes.  The first step is an
activeX script that checks the value in the table.  If it is idle or
already exporting, it should stop the package.  If it is 'waiting to
export' it should carry on running.

At first I thought that all I had to do was set the return value to be
success if I want the rest of it to run, or failure if I want it to
stop.  However, this means I get an error every 10 minutes.

So what I need is either:
1. A way of stopping the package dead in the activeX script.
2. A way of returning a value that says 'nothing went wrong, but I
still dont want you to run the next step'.

Many thanks

Kevin

Here's how I achieved a similar result:

In your ActiveX script task that checks the status, set a global variable to
a value indicating whether the package should continue or not. For example,
have a global variable called Continue, and from your ActiveX script, set to
to 'Yes' or 'No', based on the status. For example:

If <Some Condition> Then
  DTSGlobalVariables("Continue") = "Yes"
Else
  DTSGlobalVariables("Continue") = "No"
End If

Main = DTSTaskExecResult_Success

Then right Click on the next step, from the popup menu, select Work Flow >
Work Flow Properties > Option tab > Tick 'Use ActiveX Script task' > and
then Click on Properties. In this properties window you will be able to
enter an ActiveX script that checks this global variable "Continue" and
decided whether to run the package or not. Here's an example:

Function Main()
If DTSGlobalVariables("Continue") =  "Yes" Then
  Main = DTSStepScriptResult_ExecuteTask
Else
  Main = DTSStepScriptResult_DontExecuteTask
End If
End Function
I have a SQL Server 2000 server and an access front end.  I want to be
able to trigger a DTS package from Access.  I have a table that stores
a value, (idle, waiting to export, exporting).

When the user wants to trigger the package, Access puts the 'waiting to
export' value in a table.

The package is set to run every 10 minutes.  The first step is an
activeX script that checks the value in the table.  If it is idle or
already exporting, it should stop the package.  If it is 'waiting to
export' it should carry on running.

At first I thought that all I had to do was set the return value to be
success if I want the rest of it to run, or failure if I want it to
stop.  However, this means I get an error every 10 minutes.

So what I need is either:
1. A way of stopping the package dead in the activeX script.
2. A way of returning a value that says 'nothing went wrong, but I
still dont want you to run the next step'.

Many thanks

Kevin

Thats awesome!  Thanks, it works perfectly.

Just Venting
Recyle an identity column number when max is reached
Nested sets in SQL - inventor?
Performance question: Separate database or additional table?
SQL Join and Where to Put the 'ON'