分配给变量“User::de_sters"的值的类型与当前变量类型不同 [英] The type of the value being assigned to variable "User::de_sters" differs from the current variable type

问题描述

我有一个变量 de_sters，一个字符串类型的变量，因为它是在我的 SSIS 包中定义的，它的作用域是 SSIS 包，我需要用一个表中几行的值，使用Execute Sql Task包.

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.

底线是我有一个执行 Sql 任务"包，在属性 ->Sql 语句"中我写道:

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

然后，在 Result Set 我选择了 Single Row(首先我运行了我的选择，我看到它只返回一行).然后在标签 Result Set(左侧)上，我在 Result Name 字段 Result(我之前的 sql 语句的别名)和归档 变量名我写了User::de_sters.

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.

但是当我运行 sql 任务时它给了我

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.

有什么帮助或提示吗?

推荐答案

问题是 SSIS 不理解 varchar(max) 作为数据类型.你需要指定 limit . 只需将 max 值更改为 8000 .

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) = '' 

如果您的字符串非常大，则在查询中使用带有 varchar(max) 的 FullResultSet，并将值存储在数据类型为 object 的变量中.

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.

现在为了访问对象，使用 Script task 或 Script component(数据流)并编写以下代码从 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());
     }

这篇关于分配给变量“User::de_sters"的值的类型与当前变量类型不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！