从 SSIS 2012 脚本组件中的 PipelineBuffer 获取列名 [英] Getting column name from PipelineBuffer in Script Component in SSIS 2012

问题描述

我正在尝试从我的脚本组件转换 SSIS 中的 PipelineBuffer 获取列名和索引，并将它们添加到哈希表中.我知道这是可能的，如果我将我的类从:public class ScriptMain : UserComponent 更改为 ScriptMain : PipelineComponent 并使用此代码:

I'm trying to get the column name and index from the PipelineBuffer in my script component transformation is SSIS and add them to a Hashtable. I know this is possible if I change my class from:public class ScriptMain : UserComponent to ScriptMain : PipelineComponent and use this code:

public override void ProcessInput(int InputID, Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Buffer)
{
    inputBuffer = Buffer;
    hash = new Hashtable();
    IDTSInput100 i = ComponentMetaData.InputCollection.GetObjectByID(InputID);
    foreach (IDTSInputColumn100 col in i.InputColumnCollection)
    {
        int colIndex = BufferManager.FindColumnByLineageID(i.Buffer, col.LineageID);
        hash.Add(col.Name, colIndex);
    }
}

然而；当我这样做时，我不能再覆盖: public override void Input0_ProcessInputRow(Input0Buffer Row) 因为这在 PipelineComponent 类中不可用，我无法再通过简单地调用这样的东西来访问我的连接管理器:IDTSConnectionManager100 connMgr = this.Connections.DbConnection; 据我所知，BufferManager 在 UserComponent 类中不可用.有没有办法使用 UserComponent 完成此操作?

However; when I do this I can no longer override: public override void Input0_ProcessInputRow(Input0Buffer Row) Since this is not available in the PipelineComponent class and I can no longer access my connection managers by simply calling something like this: IDTSConnectionManager100 connMgr = this.Connections.DbConnection; From what I can see the BufferManager is not available in the UserComponent class. Is there a way to accomplish this using the UserComponent?

推荐答案

我的一个朋友和我一起解决了这个问题.您可以像这样获取脚本缓冲区中列的名称:

Buddy of mine worked through this with me. You can get the name of the column coming in the script buffer like this:

public override void Input0_ProcessInputRow(Input0Buffer inputBufferRow)
     {
    foreach (IDTSInputColumn100 column in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
            { 
              PropertyInfo columnValue = inputBufferRow.GetType().GetProperty(column.Name);
            }
       }

您可以通过在脚本组件中使用反射并将它们加载到过滤列表中来获取脚本缓冲区中的列索引和名称，如下所示:

You can get the column index and name in the script buffer by using reflection in the script component and loading them into a filtered list like this:

IList<string> propertyList = new List<string>();
                    var properties = typeof(Input0Buffer).GetProperties();
                    foreach (var property in properties)
                    {
                        if (!property.Name.EndsWith("_IsNull"))
                            propertyList.Add(property.Name);
                    }

然后您可以访问列表以使用 PropertyInfo 对象的名称获取脚本缓冲区中的索引值:

You can then access the list to get the index value in the script buffer using the name of the PropertyInfo object:

int index = (propertyList.IndexOf(columnValue.Name));

为了将其与输入管道缓冲区中列的索引链接起来，您需要创建一个类属性:

In order to then link this up with the index of the column in the input pipeline buffer you need to create a class attribute:

int[] BufferColumnIndexes; 

然后覆盖 ProcessInput 并添加来自输入管道缓冲区的索引，这些索引映射到脚本缓冲区索引:

Then override ProcessInput and add the indexes from the input pipeline buffer that map to the script buffer indexes:

public override void ProcessInput(int InputID, Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Buffer)
    {
        inputBuffer = Buffer;
        BufferColumnIndexes = GetColumnIndexes(InputID);
        base.ProcessInput(InputID, Buffer);
    }

现在将这些链接起来:

int index = (propertyList.IndexOf(columnValue.Name)); //index in script buffer
int index2 = (BufferColumnIndexes[index]); //index in input pipeline buffer

这篇关于从 SSIS 2012 脚本组件中的 PipelineBuffer 获取列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！