执行 SQL 任务 - 完整结果集数据类型不匹配错误 [英] Execute SQL Task -Full Result Set Datatype Mismatch Error

问题描述

我正在创建一个 SSIS 包，它有一个执行 SQL 任务，它将结果集变量传递给每个循环容器.我的 Sql 查询是:

I am creating an SSIS package which has an execute SQL task and it passes result set variable to a for each loop container. My Sql Query is:

Select distinct code from house where active=1 and campus='W'

我希望 execute sql 任务运行此查询并将其结果分配给一个变量，该变量被传递给一个 for 每个循环容器，该容器应该遍历结果集中的所有值.

I want the execute sql task to run this query and assign its results to a variable which is passed to a for each loop container which should loop through all the values in the result set.

但我的执行 sql 任务失败并出现错误:

But my execute sql task fails with error:

分配给变量的值的类型(DBNull)"User::house" 与当前变量类型不同 (String)

The type of the value (DBNull) being assigned to variable "User::house" differs from the current variable type (String)

现在我已经完成了我的研究，并尝试分配变量数据类型 Object 但没有奏效.我尝试在我的 sql 查询中使用强制转换，但也没有奏效.

Now i have done my research and i have tried assigning the variable datatype Object but did not work. I tried using cast in my sql query and that also did not work.

由于我的查询返回多行和一列，我不确定如何为整个查询分配数据类型?

Since my query returns multiple rows and one column, i am not sure how i can assign a datatype to the whole query?

示例:

• 代码
• 增强现实
• BN
• 中文

推荐答案

听起来您在这里遇到了各种各样的问题.

It sounds like you have a variety of issues in here.

第一个是在您的执行 SQL 任务中，并且需要在 Result Set 规范和结果集选项卡中指定的变量的数据类型之间达成一致.如果您指定完整结果集，则接收对象必须 是 System::Object 类型，并且您将只有 1 个结果集.所使用的连接管理器类型 (ODBC/OLE/ADO) 将决定您如何指定它，但可以在这些优质论坛上无限搜索.

The first is in your Execute SQL Task and the need for agreement between the Result Set specification and the data type of the Variable(s) specified in the Result Set tab. If you specify Full Resultset, then the receiving object must be of type System::Object and you will only have 1 result set. The type of Connection Manager (ODBC/OLE/ADO) used will determine how you specify it but it's infinitely searchable on these fine forums.

另外两个选项是单行和 XML.在使用 SSIS 的 13 年中，我从来没有理由指定 XML.这给我们留下了单行.对于单行结果集，您需要为返回的每一列提供一个变量并且需要正确键入.

The other two options are Single Row and XML. In 13 years of working with SSIS, I've never had cause to specify XML. That leaves us with Single Row. For a Single Row Result Set, you need to provide a variable for each column returned and it needs to be correctly typed.

要纠正您的问题，您需要声明第二个变量.我通常调用我的 rsObject(记录集对象)，然后将数据类型指定为 System.Object.

To correct your issue, you need to declare a second variable. I usually call my rsObject (record set object) and then specify the data type as System.Object.

然后您的 For Each 循环容器将使用Foreach ADO Enumerator"的枚举器设置，然后 ADO 对象源变量将变为User::rsObject"

Your For Each Loop Container will then be set with an Enumerator of "Foreach ADO Enumerator" and then the ADO object source variable will become "User::rsObject"

在变量映射中，您需要将变量 User::house 指定为索引 0.

In the Variable Mappings, you'll specify your variable User::house to index 0.

给定一组示例源源数据，您可以验证您的执行 SQL 任务是否正确地将结果集分配给我们的对象，并且 Foreach 循环容器是否正确填充了我们的变量.

Given a sample set of source source data, you can verify that you have your Execute SQL Task correctly assigning a result set to our object and the Foreach Loop Container is properly populating our variable.

SELECT DISTINCT
    code
FROM
(
    VALUES
        ('ABC', 1, 'w')
    ,   ('BCD', 1, 'w')
    ,   ('CDE', 0, 'w')
    ,   ('DEF', 1, 'w')
    ,   ('EFG', 1, 'x')
) house(code, active, campus)
WHERE
    active = 1
    AND campus = 'w';

如果您将校园的值从 w 更改为不存在的值，例如 f，那么事情将继续工作.

If you change the value of campus from w to something that doesn't exist, like f then things will continue to work.

但是，您收到的错误只能在代码为 NULL 时生成

However, the error you're receiving can only be generated if the code is a NULL

在 VALUES 集合中再添加一个条目，例如

Add one more entry to the VALUES collection like

    ,   (NULL, 1, 'w')

当 For Each Loop Container 达到该值时，您将遇到您指出的错误

and when the For Each Loop Container hits that value, you will encounter the error you indicate

分配给变量User::house"的值类型(DBNull)与当前变量类型(字符串)不同

The type of the value (DBNull) being assigned to variable "User::house" differs from the current variable type (String)

现在怎么办?

SSIS 变量不能改变它们的数据类型，除非它们是 Object 类型(但这不是这里的解决方案).问题"是您不能在 SSIS 变量中存储 NULL 值(除非它是对象类型).因此，您需要排除返回 NULL 的行(AND code IS NOT NULL)，或者您需要将 NULL 转换为标记/占位符值作为替代(SELECT DISTINCT ISNULL(code, '') AS 代码).如果空字符串是有效值，那么您需要找到一些无效值 - billinkcisthegreatestever10123432"不太可能存在于您的代码集中，但这可能有点过分.

Now what?

SSIS variables cannot change their data type, unless they're of type Object (but that's not the solution here). The "problem" is that you cannot store a NULL value in an SSIS variable (unless it's of type object). Therefore you need to either exclude the rows that return a NULL (AND code IS NOT NULL) or you need to cast the NULL into sentinel/placeholder value as a substitute (SELECT DISTINCT ISNULL(code, '') AS code). If an empty string is a valid value, then you need to find something that isn't - "billinkcisthegreatestever10123432" is unlikely to exist in your set of codes but that might be a bit excessive.

最后，考虑将您的 SSIS 变量从 house 重命名为 code.您也许可以保持正确，但总有一天您会将此代码交给其他人进行维护，而您不想混淆它们.

Finally, think about renaming your SSIS variable from house to code. You might be able to keep things straight but some day you'll hand this code over to someone else for maintenance and you don't want to confuse them.

如诗如画的答案https://stackoverflow.com/a/13976990/181965

这篇关于执行 SQL 任务 - 完整结果集数据类型不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！