在另一列的基础上过滤一列中的数据值，然后将值插入同一SQL表中的不同列中 [英] Filtering data values in one column based on another column and then inserting values into different columns in same SQL Table

问题描述

这是我要使用SSIS和条件拆分转换来解决的难题.我有一个.csv文件，该文件在一行中为每个唯一用户包含属性数据，在另一列中包含每个属性的值.即:

This is a bit of a conundrum I am trying to solve using SSIS and a conditional-split transformation. I have a .csv file that contains attribute data in one row for each unique user and the values for each attribute in another column. i.e.:

Attribute, Attribute Type

ID, 0000000001

Birthdate, 09/02/1976

Role, Manager

或类似的东西.我需要将属性拆分为包含属性类型数据"的列.因此，理想的结果将是:

Or something of the sort. I need to split the attributes into columns that include the Attribute Type Data. So the desired outcome would be:

ID,                    Birthdate,              Role,

0000000001,             09/02/1976,            Manager,

然后我需要将它们插入带有新列的一个SQL表中.

I then need to insert them into one SQL table with the new columns.

我能够通过对一列进行条件拆分转换(例如使用表达式Attribute =="ID"，然后将.csv源代码中的整个Attribute列映射到SQL中的ID列)来出色地完成此操作目标表)，但问题在于其他列.我似乎无法实现Union All转换来完成我想做的事情.

I was able to accomplish this beautifully with a conditional-split transformation for one column (using the expression Attribute == "ID" for example and then mapping the entire Attribute column in the .csv source onto the ID column in the SQL destination table) but the problem is doing so for the other columns. I can't seem to get a Union All transformation to do what I want it to do.

有什么建议吗?

推荐答案

您可以使用脚本组件来实现:

1. 添加脚本组件
2. 转到输入和输出"标签
3. 添加3个输出列:ID，出生日期，角色
4. 将SynchronousInput属性设置为None

1. Add a script component
2. Go to the Inputs and Outputs tab
3. Add 3 Output columns : ID, BirthDate, Role
4. Set the SynchronousInput property to None

1. 在脚本编辑器中，编写类似的脚本:

string ID = "";
string BirthDate = "";
string Role = "";
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
   if(!Row.Attribute_IsNull && !String.IsNullOrWhiteSpace(Row.Attribute))
    {

        switch (Row.Attribute)
        {

            case "ID":
                ID = Row.AttributeType;
                break;

            case "BirthDate":
                BirthDate = Row.AttributeType;
                break;

            case "Role":
                Role = Row.AttributeType;

                Output0Buffer.AddRow();
                Output0Buffer.ID = ID;
                Output0Buffer.Role = Role;
                Output0Buffer.BirthDate = BirthDate;

                break;
            default:
                break;
        }
    }
}

1. 将输出"列映射到OLE DB目标

这篇关于在另一列的基础上过滤一列中的数据值，然后将值插入同一SQL表中的不同列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！