在 SSIS 中将带有逗号分隔符的单列数据拆分为多列

2021-12-28 00:00:00 csv split sql sql-server ssis

我在 SQL Server 中有一个包含 3 列的表，其中一个是包含由逗号分隔的串联列行的数据列.第一行也是我要创建的新表的标题行.所以基本上我想转这个.

I have a table in SQL Server with 3 columns, one of which is a data column containing rows of concatenated columns delimited by commas. The first row is also the header row of the new table I want to create. so basically I want to turn this.

Data      | ID | Source 
====================
a,b,c,d,e | 1  | a.csv

f,g,h,i,j | 2  | b.csv

进入

a | b | c | d | e
=================
f | g | h | i | j

使用 SSIS，我能想到的唯一方法是使用转储到数据列的文本文件中，然后将其作为平面文件源重新读取，但我宁愿避免创建额外的不必要的文件

Using SSIS, The only way i could think of doing it is using a dump into a text file of the data column and then re-read it as an flat file source, but I'd rather avoid creating extra unnecessary files

抱歉我使用 SSIS 2008

Sorry Im using SSIS 2008

推荐答案

您可以做的是按原样读取文件.并在脚本任务中拆分这些值.

What you can do is to read the file as is. And Split those values in a script task.

所以从源代码转到脚本任务.然后在作为输入列的脚本任务中，选择包含这些值的列 (InputColumn1).然后指定输出列(如果我是对的，我看到你有 5，所以指定 5 (OutputColumn1 - 5)).

So from source go to a script task. Then in the script task as input column, select the column containing those values (InputColumn1). Then specify the output columns (If I am right I see you have 5, so specify 5 (OutputColumn1 - 5)).

完成后，转到脚本本身(C#).

After that is done, go to the script itself (C#).

在:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
}

在那里输入以下代码:

var ColumnValue = Row.InputColumn1.Split(',');

Row.OutputColumn1 = ColumnValue[0];
Row.OutputColumn2 = ColumnValue[1];
Row.OutputColumn3 = ColumnValue[2];
Row.OutputColumn4 = ColumnValue[3];
Row.OutputColumn5 = ColumnValue[4];

在脚本任务之后，Source 和 OutputCoulmns1-5 中的所有列都将可用，您可以做您必须做的事情.

After the script task all the columns from the Source as well as the OutputCoulmns1-5 will be available and you can do what you have to.

输出

 Data      | ID | Source |OutputColumn1 |OutputColumn2|  etc. 3-5
 ================================================================
 a,b,c,d,e | 1  | a.csv  |  a           |  b

 f,g,h,i,j | 2  | b.csv  |  f           |  g

有不清楚的地方请追问.

Please ask if something is not clear.

相关文章