The type of the value being assigned to variable "User::de_sters" differs from the current variable type

Question

I have a variable de_sters, a string type variable, as it is defined in my SSIS package, its scope is SSIS package, which I need to fill with the value of several lines from one table, usig an Execute Sql Task package.

Bottom line is that I have an "Execute Sql Task" package, in proprieties ->"Sql Statement" I have wrote:

declare @s varchar(max) = '' 
select @s =  case when @s <> '' 
                  then  @s + ',''' + employer_name + '''' 
                  else   @s + '''' + employer_name+ ''''
                  end 
from employers
select @s as Result

Then, at Result Set I selected Single Row (first I have ran my select and I saw that it returns only one line). Then on tab Result Set (left side) I wrote in Result Name field Result (the alias from my previous sql statement) and in filed Variable Name I wrote User::de_sters.

But when I run sql task it gives me

The type of the value being assigned to variable "User::de_sters"
differs from the current variable type" error.

Any help, or hints?

Answer 1

The problem is SSIS doesn't understand varchar(max) as a data type .You need to specify the limit .Just change max value to 8000 .

declare @s varchar(8000) = '' 

If your string is very large then use FullResultSet with varchar(max) in your query and store the value in a variable whose data type is object.

Now in order to access the object use Script task or Script component (Data Flow) and write the below code to extract the value from the object variable

OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataTable dt = new DataTable();
oleDA.Fill(dt, Dts.Variables["User::YourVariable"].Value);
 foreach (DataRow rowLoop in dt.Rows)
     {
        MessageBox.Show (rowLoop[0].ToString());
     }