How to stop running DTS package if my view return zero record?

Hi everyone,

I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.

So.. I need help from you guys, cause I don't know How.

This is my DTS description.

My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.

Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.

BUT How? Can someone please help? Thanks

Kelvin,

This is an SSIS forum (not DTS).

Sorry.

|||Not sure, and I second Rafael's comment, but can you put in a row counter against the view and test it's result?|||

I did, but I don't know how to put it into a package.?

Please Help......

|||

Kelvin Y wrote:

I did, but I don't know how to put it into a package.?

Please Help......

If you are using SSIS, and not DTS, you can use an Execute SQL task in the control flow to query the view and capture the row count. (select count(*) from view) Then in the control flow you can test the variable that was previously populated with the Execute SQL task for a condition of != 0, and if so, continue processing.|||

OK. here's another idea....slightly different from Phil's. Give it a shot. (using SSIS again)

The SQL task has a property "ForceExecutionResult" that you can configure using exprssions. Get the count from the view exactly like Phil explained above but instead of using a conditional split, use the value to set the exprssion to Success or Failure.

You wont be able to log an approiate error message though.

P.S: For some reason the use of the word "e x p r e s s i o n" is causing my post to be truncated. So, had to use "exprssion"

|||

Thanks everyone, but I can't find the "ForceExecutionResult" property.

Can you tell me where is it?

Thanks

|||

If you dont have experience with exprssions, I'd suggest using Phil's approach.

But to answer your question, you can find the property under the "misc" category about 6th in the list.

|||Thanks everyone, it works now. Thanks for helping me out.|||

Kelvin Y wrote:

Thanks everyone, it works now. Thanks for helping me out.

Please mark this thread as answered.