使用逗号分隔符将单个数据列拆分为SSIS中的多个列 [英] Split a single column of data with comma delimiters into multiple columns in SSIS

问题描述

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

  ID |源
 ================== 
 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，我想到的唯一方法是使用转储到文本文件然后重新读取它作为一个平面文件源，但我宁愿避免创建额外的不必要的文件

编辑：对不起我使用SSIS 2008

解决方案

你可以做的是读取文件。
并在脚本任务中拆分这些值。

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

完成后，转到脚本本身（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中的所有列都可用，

OUTPUT

  ID |源| OutputColumn1 | OutputColumn2 | 3-5 
 ===================================== ===================== 
a，b，c，d，e | 1 | a.csv | a | b 
 
 f，g，h，i，j | 2 | b.csv | f | g 
  

请问有什么不清楚。

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

into

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

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

EDIT: Sorry Im using SSIS 2008

解决方案

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

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)).

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

Under:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
}

Put the following code in there:

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];

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.

OUTPUT

 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.

这篇关于使用逗号分隔符将单个数据列拆分为SSIS中的多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！