2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Andy Lee Xin
  • 127
  • 5
  • I think the easiest thing to do here is to exclude or transform the NULL values (depending on the requirements). – Jacob H Nov 07 '18 at 16:41
  • 1
    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 Nov 07 '18 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. Nov 07 '18 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 Nov 07 '18 at 17:40
  • could be this as well https://stackoverflow.com/questions/38240664/assigning-value-from-single-row-result-set-in-ssis-giving-error-in-ssis-2012/38301794 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 Nov 07 '18 at 17:50
  • You must change the ResultSet type from `single row` to `Full Resultset` – Hadi Nov 07 '18 at 18:51
  • It's likely that you are assigning a NULL to a primitive type variable. That's not allowed in SSIS https://stackoverflow.com/a/53036634/181965 – billinkc Nov 07 '18 at 21:09

1 Answers1

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • This is useful when selecting all columns. When selecting particular columns a TOP # must be added to the select. But nonetheless, accepting this. – Andy Lee Xin Nov 08 '18 at 11:59