SSIS 2008 - How to check for no data returned from stored procedure

Hi guys,

I have an ssis package that has a 'EXECUTE SQL TASK' that calls a stored procedure A that returns data.  

THIS WORKS When there's data from Stored Procedure A then the downstream task Foreach Loop Container runs a Script Task which sends out an email with the subject='JOB SUCCESSFUL!' and a dynamic content with data from the STORED PROCEDURE A.

PROBLEM:  

THIS DOES NOT WORK:  When there's NO data in stored procedure A then I don't want to run the downstream  task Foreach Loop Container. How do I do that (I'm using Visual Basic 2008 in the SCRIPT TASK)?

Please show me a step by step settings/configuration/code.

NOTE: I'm using SQL SERVER 2008R2.

Thank you.
paultran00Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please find the sample below -

CREATE PROC testRow
(
	@X INT OUTPUT 
)
AS
BEGIN
	SELECT * FROM test_SQL /*Your code here */

	SELECT @x = @@ROWCOUNT

	RETURN @x
END

Open in new window


Execution with output

DECLARE @x AS INT 
EXEC testRow @x OUTPUT
SELECT @x

-----------
6

(1 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
at the end of the stored procedure you can check

@@ROWCOUNT

IF @@ROWCOUNT > 0

You can pass value from a stored procedure to the caller using OUTPUT parameter.
0
 
paultran00Author Commented:
HI. Would you please send me an example of a stored procedure that's called from ssis that runs a SELECT then also uses the rowcount as OUTPUT parameter?
0
 
paultran00Author Commented:
Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.