在脚本转换中将行转换应用于多个输入列 [英] Apply row transformation for multiple input columns in Script Transformation

问题描述

我正在尝试在具有合并单元格的SSIS中导入Excel文件，并且SSIS会将那些合并的单元格读取为NULL，而不是第一个具有数据的单元格.

I'm trying to import an Excel file in SSIS that has merged cells, and SSIS reads those merged cells as NULL other than the first cell which has the data.

我正在尝试解析信息，为此，我想将合并后的单元格数据应用于以前合并的每个单元格.

I'm trying to parse the information and for that I want to apply the merged cell data to every cell that was merged previously.

我找到了将此代码应用于F1列(在我的输入中)

I have this code that I found that applies the code to the F1 column (In my Input)

    // This script adjusts the value of all string fields
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent
{
    string filledField1 = "";

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Check if the value is null
        if (Row.F1_IsNull)
        {
            // If null => replace value by variable
            Row.F1 = filledField1;
        }
        else
        {
            // If not null => replace variable by value
            filledField1 = Row.F1;
        }
    }
}

我想要的是通过一个foreach循环将此代码应用于多个列，但是只有在我之前知道这些列的名称的情况下，我才可以这样做.

What I want is to apply this code for multiple columns through a foreach loop, but I can only do it if I know the names of the columns previously.

如何转换此代码以将转换应用于每列的每一行，而不是仅对F1列进行转换?

How can I transform this code to apply the transformation for every row of every column instead of the F1 column only?

推荐答案

您将无法对其进行泛化.如果有人知道，我将很乐意删除此内容并悬赏他们的答案.

You're not going to be able to genercize it. If someone knows otherwise, I'll be happy to delete this and bounty their answer.

之所以不能做到这一点，是因为在ScriptMain类的Input0_ProcessInputRow方法中，接受了Input0Buffer作为参数. Input0Buffer是一个自动生成的类.下面的注释位于定义Input0Buffer类的BufferWrapper.cs文件的标题

The Why it can't be done is that in your Input0_ProcessInputRow method, in ScriptMain class, accepts an Input0Buffer as the parameter. Input0Buffer is an autogenerated class. The following comment heads the BufferWrapper.cs file which defines the Input0Buffer class

/* THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!
*  Microsoft SQL Server Integration Services buffer wrappers
*  This module defines classes for accessing data flow buffers
*  THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT! */

因此，该类定义了您在ProcessInputMethod中可用的东西.对于在 单击编辑脚本"按钮之前选择的所有列，它会生成一堆getter和/或setter方法以及IsNull方法.

So, that class is what defines the things available to you in your ProcessInputMethod. It generates a bunch of getters and/or setter methods plus an IsNull method for all the columns you select before you clicked the "Edit script" button.

Input0Buffer派生自ScriptBuffer类. ScriptBuffer具有其定义

Input0Buffer is derived from the ScriptBuffer class. ScriptBuffer has as its definition

namespace Microsoft.SqlServer.Dts.Pipeline
{
    public class ScriptBuffer
    {
        protected PipelineBuffer Buffer;
        protected int[] BufferColumnIndexes;

        public ScriptBuffer(PipelineBuffer BufferToUse, int[] BufferColumnIndexesToUse, OutputNameMap OutputMap);

        protected object this[int ColumnIndex] { get; set; }

        protected void AddRow();
        protected void DirectRow(string outputName);
        protected bool EndOfRowset();
        protected bool IsNull(int ColumnIndex);
        protected bool NextRow();
        protected void SetEndOfRowset();
        protected void SetNull(int ColumnIndex);
    }
}

需要注意的重要一点是，称为Buffer的PipelineBuffer实例在ScriptBuffer类中被定义为受保护的.它在我们自动生成的类中没有定义为任何东西，因此在该类定义之外无法访问.这意味着当我们使用实例时，就像在ScriptMain中一样，我们无法触摸受保护的成员，因为它们的行为就像是私有的.

What's important to note that the PipelineBuffer instance called Buffer, is defined as protected in the ScriptBuffer class. It is not defined as anything in our autogenerated class so it's not accessible outside that class definition. That means when we're using the instance, as we do inside our ScriptMain, we can't touch the protected members as they behave as if private.

我确定您可以实现IEnumerable something接口，从而可以对Input0Buffer类执行您想做的事情，但是知道每次打开脚本时，Visual Studio都会重新生成该类并消除它您的修改.

I'm sure you could implement an IEnumerable something interface so you could do what you're wanting to do on the Input0Buffer class but know that every time you open the script up, Visual Studio is going to regenerate the class and obliterate your edits.

在键入此内容时，我在做笔记以检查扩展名方法，因为它们可能会提供一种方法...

As I type this, I am making a note to check up on how Extension Methods as they might provide an approach...

这篇关于在脚本转换中将行转换应用于多个输入列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！