February 7, 2006 at 2:08 am
I have created a DTS package.
In a particular step I am doing the following verfication on the table VW_CUSTOMER and performing an action.
if(select count(*) from VW_CUSTOMER > 0)
Perform action
What ever the count returned from the query, this step in the DTS package gets executed and it goes to the next step.
What I want to have is that whenever count in VW_CUSTOMER table is less than zero a particular step (for example step 1 ) in the DTS package has to be executed and if the count is greater than zero some other step(say step 2) in DTS package has to be executed.
ie., Can able to call a particular step in DTS package from a script written inside a step in a DTS Package
Anyone please help me.
February 8, 2006 at 3:27 am
If you have a situation where you are looking for a true or false answer (as in your qery above), the easiest way to decide which route to take in a dts package is to use the raiseerror function.
IE - If count = 0 then raiserror.
Then have two routes in your dts package. One for OnEror and another for OnSuccess.
February 8, 2006 at 6:36 am
Instead of:
if(select count(*) from VW_CUSTOMER > 0)
try:
if (select count(*) from VW_CUSTOMER) > 0
-or-
if ((select count(*) from VW_CUSTOMER) > 0)
February 8, 2006 at 7:32 am
I know this isn't part of the question, but for performance (I'm not sure how many records you have in your view...) How about:
if exists (select top 1 * from vw_Customer)
This way it doesn't have to count all the records to satisfy the if condition.
February 8, 2006 at 10:51 am
Probably just if exists (select * from vw_Customer) would be the best way to see if there's something there. Also may want to look at the code behind vw_Customer to see if there might be something even more efficient (like querying the base tables if someone built a Sort into the view or something similar).
With the routing, I'd probably use:
if exists (select * from vw_Customer)
select 1
else
select 0
As for the routing, you can call this in an ActiveX package by opening a connection to the database and choosing the correct path to take based on that value. I'd probably consider using an Exec SQL task to capture the output into a Global Variable, then test that in the ActiveX "switch". Look at http://www.sqldts.com for some examples on how to loop or skip steps based on values. You'll need two paths and you'll need to flag which one is chosen based on the value of your variable.
I'd have to dig for some examples - this gets somewhat tricky as the ActiveX to manipulate workflow was never really clean.
-Pete
February 9, 2006 at 3:17 am
For a true or false answer there is absolutely no point in setting variables. Cost time in setting up activex script and can be hard to debug. Worthwhile for complicated routes, bit not for a simple a or b.
I have been using this method for years and it works great.
IF (select count(*) from VW_CUSTOMER) = 0
begin
RAISERROR 60001 'No rows returnedfrom sql query'
end
February 9, 2006 at 4:33 am
the count(*) might cause a table scan
the exist clause stops when it finds one
February 9, 2006 at 5:12 am
Just curious as I've never used the RAISERROR to do routing, does this cause the package to report failure? If so, that might not be the desired result. It looks really fast, and yes, debugging (and programming) routing through ActiveX is not fun. However, if it causes the package to report a false failure, that could be problematic for some people.
Neat trick, though. If it doesn't cause the whole package to report failure, I may try it on something.
-Pete
February 9, 2006 at 9:57 am
The trick is what you tell it to do on failure. IE if fail go this route and if pass go to this route.
The whole point I am trying to get at is that if there are no rows returned and you expected some, then a failure also acts as a notification of the fact.
If you expect rows, it goes and does what it needs to do and registers a success.
I must admit that it is a dirty way of doing things, but it works quite well for some of my jobs.
E.g I know that my database dumps must be today's date when I come into work in the morning. The package runs at 5.00 and if the date is the previous day I raise an error and ruote it to an xp_sendmail task that notifies me.
If it's not then nothing happens.
I am pretty certain that you can include another a step to register the package as a success. I'll take a look.
February 9, 2006 at 10:00 am
Jonathan - I really like this idea! It's quick and it does the job. Thank you for sharing.
February 9, 2006 at 10:19 am
Just checked and there is no easy way I can see to go back to the step that fails and register it as a success. If you call an activex task, it does not allow you to set properties on other tasks.
However, as always, there is a sneaky way to get the same package to run and pass the second time around, although it defeats the object of making things easier.
Call the same package from within the same package a second time around using an execute package task, this time populating a variable with true. Check the variable as a first step in the package and return success at this point.
After doing that, you may as well set a variable in the first place.
February 9, 2006 at 11:00 am
Thanks. That's kind of what I suspected, but wanted to verify. I have other routing in a lot of my jobs such that we don't want the whole package to give the appearance of failure. Guess I'll be sticking with ActiveX until we can use SSIS (and perhaps even some DTS then).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply