I want to generate an error !

Question

I want to generate an error !

miguel wensch

Grasshopper

Points: 19
More actions
January 26, 2006 at 8:11 am

#67858

HI,
i have created a dts package that selects rows that have been altered in the previous hour and send them to a csv file
this works like a charm
however I have yet another thing namely
I set the records that have been altered by using a trigger and
i give a value to a field ( i called this field isupdate )
now ofcourse it is possible that no changes have been done in this hour but as the dts is scheduled to run every hour it returns a csv file without any data
I want to prevent the dts to run if there is no data in the query
so i created a stored procedure and i want to let it make an error if no data is selected in the qry so that the others steps in the dts will not be done
this is the code for the sp
CREATE PROCEDURE blablabla AS
declare @recs int
select @recs = (select count( *) from blablabla where IsUpdate = 1)
if @recs > 0
begin
select * from bla bla bla where IsUpdate = 1
end
else
begin
return(0)
end
GO
this sp i have put in the beginning of the dts design
I thought the return 0 would generate the error and thus prevent the other steps in the dts to be run
but it does not give an error in the dts
when i use the qry analyzer it shows me it doe give me the 0 when no data is selected
the dts however does not consider the code to have failed
and that is just what i want it to think
please help
I'm quite new at this

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Miquel,

You can define a workflow "On Success", so that based on the existence of records, it will proceed to the next step in the DTS package.

Thanks and have a nice day!!!

Lucky

miguel wensch Grasshopper Points: 19 More actions · Answer 2

it was already on success that he should proceed with the dts

as i said apparantely the dts does not find the query as unsuccesfull

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Hi Miguel,

SQLDTS.com has an article about conditional branching in a DTS package. I've used a version of the technique described in the article and was able to branch based on a query.

http://www.sqldts.com/default.aspx?218

Also, search sqlservercentral.com for "conditional DTS" to find some other suggestions.

Greg

miguel wensch Grasshopper Points: 19 More actions · Answer 4

thanks

i will get in to that

It looks like this is what i need

Giles McArdell SSC Eights! Points: 875 More actions · Answer 5

Why not just force the error IE:

SELECT @rc = EXEC blablabla

IF @rc = 0 RAISEERROR 'Nothing to process!', 16

Then your OnSuccess link should work

"Don`t try to engage my enthusiasm, I don`t have one."

(Marvin)