ErrorColumn值不作为沿袭ID存在 [英] ErrorColumn value does not exist as Lineage ID
问题描述
在插入目标表期间，发生的任何错误都将重定向到错误表，在该表中我们可以看到 ErrorCode 和 ErrorColumn 。问题是我们在 ErrorColumn 中获得了一个值，该值在程序包中的任何地方都不存在。即，没有一个列具有 LineageID 等于 ErrorColumn 的列。
稍后，在每个单独的列中启用NULL条目的同时，我发现是哪个列引起了问题。当我分析数据流任务中的列时，它没有在 ErrorColumn 中报告的 LineageID 。例如，报告的 ErrorColumn 是413，但是 LineageID 在第一次合并期间是84，并且在各种排序过程中都会改变。无论如何，它永远不会变成413。此 ErrorColumn ID（413）根本不存在，但在将错误重定向插入到目标源（错误表）的过程中被报告。
我检查了许多站点，但它们都建议通过 ComponenteMetaData.InputCollection 或 LineageID ，但这没有成功。
正如我所说，我已经解决了问题，但是由于我们处于ETL流程的早期阶段，因此在其他情况下可能会发生这种情况。
我正在复制我的答案，以便我们可以在该网站上获得权威的问答。
。异步转换导致将数据从一组黄油复制到另一组黄油，从而导致...分配了新的血统ID，因此即使使用联合将两个流重新组合在一起，您也无法调用数据流
在这一点上，我承认失败并决定我可以在没有智能/有用的错误报告的情况下生活。
2012
2012年发布的SSIS更改了他们使用LineageID保持列同步的方式。组件没有将元素从源映射到接收器，而是使用元素的文本表示形式。上面引用的XML现在看起来像
< outputColumn
refId = Package\DFT Generate Errors\ DER除以SomeNumber.Outputs [派生列输出] .Columns [LookAtMe]
dataType = i4
errorOrTruncationOperation = Computation
errorRowDisposition = RedirectRow
lineageId = 包-DFT生成错误-DER除以SomeNumber.Outputs [派生列输出] .Columns [LookAtMe]
name = LookAtMe
truncationRowDisposition = FailComponent>
如果您现在查看ErrorColumn，他们甚至都不会引用文本沿袭。相反，它们引用第6列。如果我搜索源XML，就不会在任何地方找到对第6列的引用。
During the insert into a destination table, any error that occurs is redirected to Errors table where we can see the ErrorCode and ErrorColumn. The problem is that we got a value in ErrorColumn which does not exist anywhere within the package. Namely, there is not a single column that has LineageID that is equal to ErrorColumn.
Later, while enabling NULL entry in every single column, one by one, I found which column caused the problem. When I analyzed the column inside of a Data Flow task it did not have the LineageID that was reported in an ErrorColumn. For example, the ErrorColumn reported was 413, but LineageID is 84 during the first merge, and it changes during various sortings. Regardless of that, it never becomes 413. This ErrorColumn ID (413) does not exist at all yet it is reported during the error redirection insert into destination source (Error table).
I've checked many sites, but they all suggested enumerating through ComponenteMetaData.InputCollection or ComponentMetaData.OutputCollection in a script task, and then enumerating through columns in order to find LineageID, but it was without any success.
As I said, I've solved the problem, but as we are in the early stage of ETL process, this might happen in some other cases. How can this problem be tackled?
I'm copying my answer so we can get an authoritative Q&A on the site
What is the simple way to find the column name from Lineageid in SSIS
I remember saying this can't be that hard, I can write some script in the error redirect to lookup the column name from the input collection.
string badColumn = this.ComponentMetaData.InputCollection[Row.ErrorColumn].Name;
What I learned was the failing column isn't in that collection. Well, it is but the ErrorColumn reported is not quite what I needed. I couldn't find that package but here's an example of why I couldn't get what I needed. Hopefully you will have better luck.
This is a simple data flow that will generate an error once it hits the derived column due to division by zero. The Derived column generates a new output column (LookAtMe) as the result of the division. The data viewer on the Error Output tells me the failing column is 73. Using the above script logic, if I attempted to access column 73 in the input collection, it's going to fail because that is not in the collection. LineageID 73 is LookAtMe and LookAtMe is not in my error branch, it's only in the non-error branch.
This is a copy of my XML and you can see, yes, the outputColumn id 73 is LookAtme.
<outputColumn id="73" name="LookAtMe" description="" lineageId="73" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Computation" errorRowDisposition="RedirectRow" truncationRowDisposition="RedirectRow" externalMetadataColumnId="0" mappedColumnId="0"><properties>
I really wanted that data though and I'm clever so I can union all my results back together and then conditional split it back out to get that. The problem is, Union All is an asynchronous transformation. Async transformations result in the data being copied from one set of butters to another resulting in...new lineage ids being assigned so even with a union all bringing the two streams back together, you wouldn't be able to call up the data flow chain to find that original lineage id because it's in a different buffer.
Around this point, I conceded defeat and decided I could live without intelligent/helpful error reporting in my packages.
2012
The 2012 release of SSIS changed how they used LineageID to keep columns in sync. Instead of components mapping a number from a source to sink, they went with a textual representation of the element. The XML referenced above would now look like
<outputColumn
refId="Package\DFT Generate Errors\DER Divide by SomeNumber.Outputs[Derived Column Output].Columns[LookAtMe]"
dataType="i4"
errorOrTruncationOperation="Computation"
errorRowDisposition="RedirectRow"
lineageId="Package\DFT Generate Errors\DER Divide by SomeNumber.Outputs[Derived Column Output].Columns[LookAtMe]"
name="LookAtMe"
truncationRowDisposition="FailComponent">
If you look at the ErrorColumn now, they don't even reference the textual lineageid. Instead, they reference column 6. If I search through the source XML, I'm not going to find a reference to Column 6 anywhere. It must be some run-time magic.
The net result unfortunately is the same - you cannot access the error column because it's being created in this component because it only exists in the Output columns collection. It isn't available in the Error Columns collection.
这篇关于ErrorColumn值不作为沿袭ID存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
