Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DTS workflow branching based on query results

Posted on 2006-10-27
6
511 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:garycris
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17822067
do you mean NULL or just empty / false?

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

e.g.

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

hth
0
 
LVL 5

Author Comment

by:garycris
ID: 17822129
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

Thanks
0
 
LVL 5

Author Comment

by:garycris
ID: 17822361
I've been giving this more thought and I think I could just say

SELECT COUNT(*)
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?
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 11

Expert Comment

by:rw3admin
ID: 17822690
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

rw3admin
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 17824190
1. Create a Global Variable called MyCount data type integer.
2. Create an Execute SQL Task with:
SELECT COUNT(*) MyCount
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
Else
      Main = DTSTaskExecResult_Failure
End If

End Function
0
 
LVL 5

Author Comment

by:garycris
ID: 17836513
Exactly what I wanted.  Works perfect.

Thanks,
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Virtual SQL Server 2014 Standard 35 88
tools to scan a SQL server's problem 14 26
Run an action on recently added records to a table 13 60
SQL Query 2 31
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question