Kelvin Y


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





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

Rafael Salas


Kelvin,

This is an SSIS forum (not DTS).

Sorry.







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

Phil Brammer

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






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

Kelvin Y

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

Please Help......






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

Phil Brammer

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.





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

Ravi G

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"





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

Kelvin Y

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

Can you tell me where is it

Thanks






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

Ravi G

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.





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

Kelvin Y

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




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

Phil Brammer

Kelvin Y wrote:
Thanks everyone, it works now. Thanks for helping me out.


Please mark this thread as answered.