CODEWITHSUNDEEP
sqlsql-serverssisetlsql-server-data-tools
Andy Lee Xin
Andy Lee Xin

Reputation: 127

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

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?

enter image description here

enter image description here

Upvotes: 2

Views: 767

Answers (1)

Hadi
Hadi

Reputation: 37313

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

Upvotes: 2

Related Questions