从 ssis 中的单行结果集中分配值在 SSIS 2012 中给出错误 [英] Assigning value from single row result set in ssis giving error in SSIS 2012

问题描述

我正在尝试从 sql server 的表中获取 CSV ID，并将结果分配给一个变量.下面是我放在执行 SQL 任务

I am trying to get CSV IDs from a table from sql server and assign the result to a variable. below is the sql I have put inside the Execute SQL Task

set nocount on
declare @csv varchar(max) = ''
select @csv = @csv + cast(companyid as varchar(10)) + ',' from company where isprocessed = 0
select substring(@csv,1,len(@csv) - 1) as companyids

如您所见，这是一种在 t-sql 中获取字段 csv 的简单而标准的方法.它在查询窗口中完美运行，但在 SSIS 2012 中运行任务时抛出以下错误

As you can see its a simple and standard way of getting csv of a field in t-sql. It works perfectly in query window but throwing below error when I run the Task in SSIS 2012

[执行 SQL 任务] 错误:值类型 (__ComObject) 只能是转换为 Object 类型的变量.

[Execute SQL Task] Error: The value type (__ComObject) can only be converted to variables of type Object.

[执行 SQL 任务] 错误:给赋值时发生错误变量 "sCSVCompanyIds": "值的类型 (DBNull)分配给变量User::sCSVCompanyIds"与当前的不同变量类型(字符串).在此期间变量可能不会改变类型执行.变量类型是严格的，类型的变量除外目的.".

[Execute SQL Task] Error: An error occurred while assigning a value to variable "sCSVCompanyIds": "The type of the value (DBNull) being assigned to variable "User::sCSVCompanyIds" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

以下是执行 SQL 任务的设置

Below are the settings of Execute SQL Task

1. 在常规选项卡中，结果集项目设置为单行
2. 在结果集选项卡中，结果名称设置为0(我也尝试将其设置为csvids，这是选择列表中的别名列名)和变量名称设置为 User::sCSVCompanyIds

1. In General tab, Resultset project is set to single row
2. In Result Set tab, Result Name is set to 0 (I also tried by setting it to csvids which is the alias column name in the select list) and Variable Name is set to User::sCSVCompanyIds

我不知道为什么它不起作用.在浪费了这么多时间之后，我找到了一种艰难的方法，即通过将结果作为全行集返回(始终返回 1 行 1 列的相同 SQL)并为每个循环容器添加一个循环抛出结果集(它将始终迭代仅出于明显原因一次)并将结果集的字段分配给变量.它对我有用，但应该有简单的方法.我缺少什么?

I have no clue why its not working. After wasting so much time I am worked out a hard way which by returning the result as Full Row set (same SQL which returns 1 row 1 column always) and add a for each loop container to loop throw the result set (which will always iterates only once for obvious reasons) and assign the result set's fields to the variable. It works for me but there should be easy way of doing it. What I am missing?

推荐答案

问题在于 nvarchar(max) 数据类型.我认为 varchar(max) 也是一样的.尽管 SSIS 中的相关数据类型是 DT_NTEXT 和 DT_TEXT 由于某种原因存在，但我们收到此错误.

The problem is with the nvarchar(max) data type. I assume it will be same for varchar(max) also. Though relevant data types in SSIS are DT_NTEXT and DT_TEXT are present for some reason we are getting this error.

有多种方法可以解决这个问题.

There are multiple options to handle this.

一个 当然，通过将查询中的列强制转换/转换为固定长度而不是像 cast(myvarcharmaxfield as varchar(8000)) 之类的 max，当然可能有更合适的方法来处理此问题.在我的情况下它不起作用，因为我希望更长的字符串.我正在从一个唯一标识符列生成一个 csv 字符串，该列本身是 36 个字符的长字符串，需要 3 个额外的字符来用单引号和逗号作为分隔符引用它们，该列仅支持 205 个值.所以它对我不起作用.

One Of course there might be more appropriate way to handle this by cast/converting the column within the query to fixed length instead of max something like cast(myvarcharmaxfield as varchar(8000)). In my case it doesn't work because I expect bigger length string. I am generating a csv string from a unique identifier column which itself is 36 chars long string and needs 3 extra chars for quoting them with signle quote and a comma as separator which will support only 205 values. So it doesn't work for me.

所以我别无选择，只能坚持我已经实施的方式，这是我的问题

So I left with no option but to stick the way I implemented already which is in my question

在浪费了这么多时间之后，我找到了一条艰难的路将结果作为完整行集返回(返回 1 行 1 的相同 SQL始终列)并为每个循环容器添加一个以循环抛出结果集(由于显而易见的原因，它总是只迭代一次)并将结果集的字段分配给变量

After wasting so much time I am worked out a hard way which by returning the result as Full Row set (same SQL which returns 1 row 1 column always) and add a for each loop container to loop throw the result set (which will always iterates only once for obvious reasons) and assign the result set's fields to the variable

我学到的新方法来自这个问题的未接受答案

将 Oledb 连接更改为 ADO.NET 连接

Change the Oledb connection to ADO.NET connection

我认为这足够公平，但它需要我创建另一个连接管理器(这样我就不必更改所有现有任务)并将其用于我需要 csv 但我没有购买的此类任务因为我几乎没有时间研究创建它的连接字符串，在我看来，我得到的错误类型与 oledb 不同.

I think this is fair enough but it requires me to create another connection manager (so that I don't have to change all existing tasks) and use it for this type of taks where I need csv but I did not buy it as I have very little time to doing research on creating connection string of it which appears to me with the type of erros I am getting is different from oledb.

这篇关于从 ssis 中的单行结果集中分配值在 SSIS 2012 中给出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！