paultran00
asked on
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/cod e.
NOTE: I'm using SQL SERVER 2008R2.
Thank you.
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/cod
NOTE: I'm using SQL SERVER 2008R2.
Thank you.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much.
@@ROWCOUNT
IF @@ROWCOUNT > 0
You can pass value from a stored procedure to the caller using OUTPUT parameter.