DTS Package - Execute Sql Task

Hello
Could someone please tell me what constitutes a "success" and a "failure"
result when running an "Execute Sql" task within a DTS package.
For instance, within my DTS package, I have an Execute Sql task which simply
runs a stored procedure and returns a scalar value which is the number of
records in a table (ie SELECT COUNT(1) FROM TABLENAME).
Now if that value is below a predermined value (global variable), I want the
Execute Sql task to report "success" and then carry on to execute an ActiveX
task using the "On Success" constraint within the package workflow. If the
value is not below the predetermined value, I wantthe Execute Sql task to
report "failure" and just stop there.
I hope this isn't confusing.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--In your Excecute SQL task, you need to evaluate the scalar value returned
from the stored proc and, if desired, raise an error. This will force the
DTS workflow to the 'failure' route.
Something like this:
if @.var >= @.predeterminedvalue
RAISERROR ('Value was too high',16,1)
Then put an ActiveX script in the failure workflow and make the script one
line:
Main = DTSTaskExecResult_Failure
If you want the package to end after this, then just make sure there are no
other tasks in the workflow after the ActiveX script AND there are no tasks
that run without a predecessor.
"Peter" <spam@.dwstech.com> wrote in message
news:1139320625_5345@.sp6iad.superfeed.net...
> Hello
> Could someone please tell me what constitutes a "success" and a "failure"
> result when running an "Execute Sql" task within a DTS package.
> For instance, within my DTS package, I have an Execute Sql task which
> simply runs a stored procedure and returns a scalar value which is the
> number of records in a table (ie SELECT COUNT(1) FROM TABLENAME).
> Now if that value is below a predermined value (global variable), I want
> the Execute Sql task to report "success" and then carry on to execute an
> ActiveX task using the "On Success" constraint within the package
> workflow. If the value is not below the predetermined value, I wantthe
> Execute Sql task to report "failure" and just stop there.
> I hope this isn't confusing.
> Regards
> Peter
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Excellent VC
That is kinda what I was thinking but just wasn't sure if that was an
accepted method.
I could also, of course, put the whole thing in an ActiveX script but then
it gets nasty because I would have to create connection and recordset
objects etc just to return the one scalar (rowcount).
Thanks
Peter
"VC" <me@.here.com> wrote in message
news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
> In your Excecute SQL task, you need to evaluate the scalar value returned
> from the stored proc and, if desired, raise an error. This will force the
> DTS workflow to the 'failure' route.
> Something like this:
> if @.var >= @.predeterminedvalue
> RAISERROR ('Value was too high',16,1)
> Then put an ActiveX script in the failure workflow and make the script one
> line:
> Main = DTSTaskExecResult_Failure
> If you want the package to end after this, then just make sure there are
> no other tasks in the workflow after the ActiveX script AND there are no
> tasks that run without a predecessor.
>
> "Peter" <spam@.dwstech.com> wrote in message
> news:1139320625_5345@.sp6iad.superfeed.net...
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter - Yes, putting the whole thing in an ActiveX script is possible, but I
personally think that is a bad design. I hate the idea of a DTS package
sitting right on the SQL Server running a script that creates an ADO
connection to the SQL Server itself in order to execute a simple SQL. It is
just a lot of unnecessary overhead.
"Peter" <spam@.dwstech.com> wrote in message
news:1139325919_5391@.sp6iad.superfeed.net...
> Excellent VC
> That is kinda what I was thinking but just wasn't sure if that was an
> accepted method.
> I could also, of course, put the whole thing in an ActiveX script but then
> it gets nasty because I would have to create connection and recordset
> objects etc just to return the one scalar (rowcount).
> Thanks
> Peter
> "VC" <me@.here.com> wrote in message
> news:bpqdnbU5164MJHXenZ2dnUVZ_tGdnZ2d@.co
mcast.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--