SSIS 查找步骤失败且没有无意义的合并所有步骤
想象一个 SSIS 包，它按 A 列进行查找，A 列可以为空，如果 A 为空，则按 B 列和 C 列（B 列和 C 列不能为空）进行查找。就 SSIS 步骤而言，按如下方式完成：
1) 条件拆分，其中条件 1 为“ISNULL(ColumnA)”。条件 1 输出名为 ISNULL，默认输出名为 NOTNULL。
2a) NOTNULL 输出指向查找任务。查找任务执行与 ColumnA 的值匹配的 SQL 查询。它没有被缓存。
2b) ISNULL 输出定向到查找任务。查找任务执行与 ColumnB 和 ColumnC 的值匹配的 SQL 查询。它也不被缓存。
3) 两次查找都指向联合任务并且流程继续。
我目前正在开发一个包含此工作流程的包，但该包失败了。提供的唯一错误消息是：
“[查找 [7708]] 错误：SSIS 错误代码 DTS_E_OLEDBERROR。发生 OLE DB 错误。错误代码：0x80040E21。 OLE DB 记录可用。来源：“Microsoft SQL Server Native Client 10.0” Hresult：0x80040E21 描述：“无效的输入参数值。请检查状态值以了解详细信息。”
奇怪的是，如果我将 NOTNULL 条件的输出定向到 Union All 任务，然后到查找中，一切都很完美，我不知道为什么这会影响结果，似乎具有一个输入的 Union All 任务应该对数据没有影响
。能够在这个特定的包之外重现这个问题。我尝试使用相同的源数据和相同的数据库创建一个示例包，并且我不
希望任何人抛出 这个错误。能够阅读此内容并立即知道问题所在。但是，我希望有人知道可能适用的 SSIS 已知问题，或者知道如何从该错误消息中获取更多详细信息的人（我不知道是什么。它的意思是“检查状态值以了解详细信息。”）
更新：根据请求提供一些附加信息。
SSIS 是在 VS2008 中运行的版本 2008 R2
目标 SQL Server 是 2008 SP2 （不是 R2）
A 列是 DT_I8/bigint，B 列是 DT_STR(18)/varchar(18)，C 列是 DT_STR(25)/varchar( 25）。
还有一个背景数据可能有助于激发新的想法。该包的工作原理是尝试在具有唯一约束的表上执行 OLEDB 插入，如果失败，它将失败行重定向到执行更新的部分。我上面描述的步骤是在插入失败后尝试执行更新的部分。如果我删除插入并直接进行更新，则包开始工作。但是，我尝试在测试包中复制这一部分，但它根本没有失败，所以我不能确定这就是问题所在。
Imagine an SSIS package which does a lookup by Column A, which can be null, or columns B and C if A is null (columns B and C cannot be null). In terms of SSIS steps, this is done as follows:
1) A Conditional Split where Condition 1 is "ISNULL(ColumnA)". Condition 1 output is named ISNULL, Default output is named NOTNULL.
2a) The NOTNULL output directs to a lookup task. The lookup task does an SQL query matched to the value of ColumnA. It is not cached.
2b) The ISNULL output directs to a lookup task. The lookup task does an SQL query matched to the values of ColumnB and ColumnC. It is also not cached.
3) Both lookups direct to a union task and flow continues.
I am currently working on a package containing this workflow and the package is failing. The only error message provided is:
"[Lookup [7708]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Invalid input parameter values. Check the status values for detail."."
Strangely, if I direct the output from the NOTNULL condition into a Union All task, and then into the lookup, everything works perfectly. I have no idea why this would affect the outcome. It seems like a Union All task with one input should have no effect on the data.
Even more strangely, I have not been able to reproduce this problem outside of this particular package. I have tried creating a sample package with the same workflow, using the same source data and the same database, and it does not throw this error.
I don't expect anyone to be able to read this and know immediately what the problem is. However, I am hoping that someone knows of a known issue with SSIS that may apply, or anyone who knows how to get more details from that error message. (I have no idea what it means by "Check the status values for detail.")
Update: Providing some additional information by request.
SSIS is version 2008 R2 running in VS2008
The target SQL Server is 2008 SP2 (NOT R2)
Column A is a DT_I8/bigint, Column B is DT_STR(18)/varchar(18), Column C is DT_STR(25)/varchar(25).
There is one more piece of background data that may help spark a new thought. This package works by trying to do an OLEDB insert on a table with a unique constraint, and if that fails it redirects the failure rows to a section that does an update instead. The steps I described above are the piece that tries to do the Update after a failed insert. If I remove the Insert and go straight to an update the package starts to work. However, I tried to replicate just this section in a test package and it didn't fail at all, so I can't be sure this is the issue.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
2008年，不是2005年吧？简短的回答是我认为你的元数据被劫持了。
在条件拆分操作之前右键单击绿线连接器，并记下 A、B 和 C 列的数据类型和长度/精度。将其与 Union All 的输出进行对比。 A 的类型必须在两点之间发生变化，才会有这样的行为（或者我只是赌了一些声誉）。问题可能出在数据流的更高层，它似乎认为数据类型不正确。
一般来说，引擎擅长检测变化，但有时它不会收到信号，通常是当大小发生变化并且源和受影响的转换之间有一些异步组件时。
如果不是这种情况，数据流中 A 的数据类型是什么，目标数据库（还有 SQL Server？）中该列的数据类型是什么，您能否提供查找查询？此外，修改失败的查找，将出错的行重定向到位存储桶（派生列、行计数、脚本任务），并在其上放置数据查看器。违规行的值是多少？目标数据库中是否存在该内容？查找区分大小写，即使数据库不区分大小写。
2008, not 2005 right? The short answer is I think your meta data is jacked.
Right click on the green line connector before your conditional split operation and note the data types and lengths/precision for columns A, B and C. Contrast that with the output of the Union All. The type of A must have changed between the two points for it to be behaving like this (or I've just gambled some reputation away). The problem might be higher up in the data flow where it seems to think it has an incorrect data type.
Generally, the engine is good about detecting changes but sometimes it doesn't get the signal, usually when there are size changes and you've got some async components between the source and the affected transform.
If that's not the case, what is the data type for A in the data flow and what is the data type in the target database (SQL Server as well?) for this column and can you provide the lookup query? Also, modify the failing lookup to redirect rows on error to a bit bucket (derived column, row count, script task) and drop a data viewer on that. What is the value of the offending row? Does that exist in the target database? Lookups are case sensitive, even if the database is not.