I have a DTS package in Sql 2000 where I select records from a table and
then place them into an Excel table and email that to the users. If there
are no records, I don't want to email the users anything but I do want the
administrator to know that no records existed.

So, is there a way that if no records exist, to change the workflow of the
DTS? Workflow only has On Success, On Failure and On Completion. Does a
select statement that returns no rows be considered a failure?

Thanks
Mike

Hello The other Mike,

In your pkg you could design two separate workflows (one will work if you
'll find rows the other if you won't ritrieve any row), each one starting
with a default disabled step( ex: call them stp1 and stp2)
In the same pkg you could use a starting SQL task wich will count the rows
of interest and put this number in a Global Variable;this task will be followed
by an ActiveX script which, basing on the value of the Global variable, will
decide to activate stp1 OR stp2 using a script like this:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
MsgBox "Count rows"



Dim gvs 'As DTSGlobalVariables
Dim gva 'As DTSGlobalVariable
Dim pkg 'As DTSGlobalVariables.parent
Dim stp 'As Step



Set gvs = DTSGlobalVariables
Set pkg = DTSGlobalVariables.parent

if( gvs("NumRows").Value > 0) then
set stp = pkg.Steps("stp2")
else
set stp = pkg.Steps("stp3")
end if
stp.DisableStep = False
stp.ExecutionStatus = DTSStepExecStat_Waiting
set stp = nothing
Main = DTSTaskExecResult_Success
End Function

On http://www.sqldts.com you'll surely find better examples; anyway,if
you need i can send you my test example.

Bye

Thats perfect.

Thank you very much.

"Davide Rossetti" <rossetti71 (AT) gmail (DOT) com> wrote

Hello The,


So what I would do is this.

I would have an ExecuteSQL task which asked the COUNT(*) question and assigned
the value to a Global Variable. I would then use a Script Task to retrieve
the value and decide which workflow path to take.

We do a similar thing here with files.

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)


Allan