• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

DTS workflow branching based on query results

I have DTS package that runs an Execute Process Task.  Prior to running the task I want to check results from a couple of queries that I have.  Basically the queries are stored views that check for missing members in certain tables.  The queries should always come back with a null result, in which case the Process Task should run, if however one of the views does return a record (1 or 1000 records, doesn't make a difference) then the Process Task should not run.
I'm figuring I need a VBscript or maybe a stored procedure, but I'm not very good with either so please give me some good ideas and provide a lot of details on how to implement.

Tanks in advance.
1 Solution
do you mean NULL or just empty / false?

sounds like you want to do an Exists/Not Exists test...


  If not Exists ( select 'y' from View1 where ...)
    amd not exists ( select 'y' from View2 where ...)
        do what you want

garycrisAuthor Commented:
An example would be I have a view called Missing_Accounts.

I want to write an ActiveX VB script that checks if the query returns any records.

If it returns records, I want to DTSTaskExecResult_Failure
If it doesn't return any records, then I want DTSTaskExecResult_Success

garycrisAuthor Commented:
I've been giving this more thought and I think I could just say

FROM Missing_Acocunts

and then say if the result is 0 then success if <> 0 then failure.

So how do I run a query in an active x script and capture the result to test in an IF statement?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

you can do
Select 1/(SELECT COUNT(*)
FROM Missing_Acocunts

if there are records this will succeed, if there are no records ( 0 records ) you will get 0 divided error and then you can go to your two execute process tasks on Succes and on Failure

Anthony PerkinsCommented:
1. Create a Global Variable called MyCount data type integer.
2. Create an Execute SQL Task with:
FROM Missing_Acocunts
3. Click Parameters ...
4. Select the Output tab
5. Select Row Value option
6. Match the MyCount Parameter to the MyCount global variable.
7. Create an ActiveX Script Task and make sure to use Workflow so that it executes after the Execute SQL Task.
8. Add code as follows:
Function Main()

If  DTSGlobalVariables("MyCount").Value > 0 Then
      Main = DTSTaskExecResult_Success
      Main = DTSTaskExecResult_Failure
End If

End Function
garycrisAuthor Commented:
Exactly what I wanted.  Works perfect.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now