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?

I think the easiest thing to do here is to exclude or transform the NULL values (depending on the requirements). — Jacob H
– Jacob H, Commented Nov 7, 2018 at 16:41
Most likely you are getting multiple results from the query, with an execute sql task set as ResultSet=Single row and attempting to assign multiple rows to a single variable. If you manually run the query in management studio with the like operator do you get multiple rows? If you do, there's the issue. — Tim Mylott
– Tim Mylott, Commented Nov 7, 2018 at 17:21
@Andy Lee Xin, can you tell us and show us how you have the results mapped to the variables, i.e. Result Set tab? You are not explicitly listing the columns in your query, and I bet there is some mix up on the column you are trying to assign to the variable. — Jeremy J.
– Jeremy J., Commented Nov 7, 2018 at 17:28
@JeremyJ.You're right, I was in the middle of chiming back in to basically ask what you just asked. What's the result set Tab. Also sample set of what the query is returning would be helpful as well. There's a mix up somewhere. — Tim Mylott
– Tim Mylott, Commented Nov 7, 2018 at 17:40
could be this as well stackoverflow.com/questions/38240664/… so whatever column is getting assigned on the result set tab might be either a text or varchar(max) datatype. Check that as well. — Tim Mylott
– Tim Mylott, Commented Nov 7, 2018 at 17:50

Hadi · Accepted Answer · 2018-11-07 18:55:16Z

2

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

answered Nov 7, 2018 at 18:55

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andy Lee Xin Over a year ago

This is useful when selecting all columns. When selecting particular columns a TOP # must be added to the select. But nonetheless, accepting this.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related