SSIS Execute SQL Task works but displays error message in execution report

Question

My Execute SQL Task executes a stored procedure. If it gets some records, then proceeds to a task to create a report. Otherwise, it goes to a script task to send out an email. The ResultSet is set to "Single row".

Everything works as expected. However, when stored procedure does not return any rows, it still sends out an email, but creates a failed message in SQL Execution report. The error message states:

An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned."

I cannot modify my stored procedure. What are my options to "fix" this error. (My package runs as expected.)

Answer 1

You have to add some validation to your Execute SQL Task to avoid Empty ResultSets

1. Insert result into a Temp Table
2. Check If the Temp Table contains Rows

3. If not Select an non significant Row

   CREATE #TempTable (Column0 Varchar(50), Column1 varchar(50), ....)
   
   INSERT INTO #TempTable
   EXEC Proc
   
   DECLARE @intCount INT
   
   SELECT @intCount = COUNT(*) FROM #TempTable
   
   IF @intCount > 0
   SELECT TOP 1 * FROM #TempTable
   ELSE
   SELECT 0
   

4. Add An expression on The Send Mail Task (If Not Empty Row --> Send Mail)