Need Execute SQL component to fail package if zero records

1Dave


I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this I had a raise error statement if @@rowcount was zero but this did not fail the component. Any other suggestions

Rafael Salas


A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.

1Dave

I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.

Rafael Salas

I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.

1Dave

I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.