SSIS ExecuteSQL task error with ResultSet

Question

Hi I am trying to return a ResultSet from an "Execute SQL Task" in SSIS. I am then trying to save the result in a "ResultSet" variable. My Query works in the console and my connection is ok to.

Here is my Query SELECT Src FROM [myDB].[dbo].[myTable] group by Src

Src is nvarchar(255)

When I execute the task I am getting following error [Execute SQL Task] Error: Executing the query "SELECT Src FROM [myDB].[dbo].[myTable..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::ResultSet" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I am also attaching the screen shots for the Task setup.

Please help. ExecuteSQL Screen1

ExecuteSQL Screen2 - ResultSet setup

Answer 1

In SSIS 2012 within a ForEach loop using an ADO enumerator, you are unable to assign a null value to a variable of type String when the source value came from a SQL query returning a null value.

I would suggest Derived Column Transform feature of SSIS you'll use the SSIS ISNULL function to detect the NULL value - and then do something with it.

More info about Derived Column Transform

For example transforming String column:

ISNULL([String-Column]) ? "There Was No Value" : [String-Column]