SSIS Cannot assign to variable with LIKE in the direct input query

Question

I have an Execute SQL Task wherein I execute the following direct input:

select * from test
where FName like '%Alex%'

and assign it to a user variable named User::NameSelected.

However, when I run the query, I am getting the following error:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "NameSelected": "The type of the value (DBNull) being assigned to variable "User::NameSelected" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.".

When I run a query without the LIKE, it can return a single row. But I need to get a result from the query with LIKE.

Is it possible that I can do it via a Direct input query?

Answer 1

You must change the ResultSet type from Single Row to Full ResultSet, and store the result in a variable of type System.Object. Then you should use a script task to read the values from the variable.

Helpful links

• SSIS - How to access a RecordSet variable inside a Script Task
• SSIS Basics: Using the Execute SQL Task to Generate Result Sets
• Reading Execute SQL resultset from Script Task