从 SSIS 中的 Lineageid 中查找列名的简单方法是什么 [英] What is the simple way to find the column name from Lineageid in SSIS

问题描述

在 SSIS 中从 Lineageid 中查找列名的简单方法是什么.有没有可用的系统变量?

What is the simple way to find the column name from Lineageid in SSIS. Is there any system variable avilable?

推荐答案

我记得说过这不会那么难，我可以在错误重定向中编写一些脚本来从输入集合中查找列名.

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;

我了解到失败的列不在该集合中.嗯，是的，但是报告的 ErrorColumn 并不是我所需要的.我找不到那个包裹，但这里有一个例子，说明为什么我找不到我需要的东西.希望你能有更好的运气.

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.

这是一个简单的数据流，由于被零除，一旦到达派生列就会产生错误.Derived 列生成一个新的输出列 (LookAtMe) 作为除法的结果.错误输出上的数据查看器告诉我失败的列是 73.使用上述脚本逻辑，如果我尝试访问输入集合中的第 73 列，它将失败，因为它不在集合中.LineageID 73 是 LookAtMe，LookAtMe 不在我的错误分支中，它只在非错误分支中.

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.

这是我的 XML 的副本，您可以看到，是的，outputColumn id 73 是 LookAtme.

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>

虽然我真的很想要这些数据，而且我很聪明，所以我可以将所有结果重新组合在一起，然后有条件地将其拆分出来以得到它.问题是，Union All 是一个异步转换.异步转换导致数据从一组黄油复制到另一组，从而导致...分配新的沿袭 ID，因此即使使用联合将两个流重新组合在一起，您也无法调用数据流链以查找原始谱系 ID，因为它位于不同的缓冲区中.

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.

这篇关于从 SSIS 中的 Lineageid 中查找列名的简单方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！