[Execute SQL Task] Error: An error occurred while assigning a value to variable "XYZ": "Single Row result set is specified, but no rows were returned."

This error occurred when execute sql task returning the no record and we are trying to store this value into the Single Row Result set.

Let’s see this demo

This is my table

     

And we want to set get the emp number based on the city. I am writing a simple sql script.

 

Now I am taking execute sql task

 

Storing this value into the variable.

Always it will return one record so I am using single row.

Now I am running.

Executed successfully.

It will throw an error, when it will return no record. See below.

Now running the same

 

Failed with below error.

[Execute SQL Task] Error: An error occurred while assigning a value to variable "XYZ": "Single Row result set is specified, but no rows were returned."

To overcome this issue we need to take ResultSets as full result set and store this value into the object type variable.

I am creating an object type of variable.

Now I am running this task.

Task executed successfully.