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.
我在SQL Server中有一个包含3个列的表,其中一个是包含被逗号分隔的连接列的数据列。第一行也是我要创建的新表的标题行。所以基本上我想把这个转变。
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
使用SSIS,我能想到的唯一方法是将转储文件放入数据列的文本文件中,然后将其重新读取为一个平面文件源,但我宁愿避免创建额外的不必要的文件。
EDIT: Sorry Im using SSIS 2008
编辑:对不起,我使用SSIS 2008。
2 个解决方案
#1
4
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)).
所以从源代码到脚本任务。然后在脚本任务中作为输入列,选择包含这些值的列(InputColumn1)。然后指定输出列(如果我是对的,我看到您有5个,所以指定5 (OutputColumn1 - 5))。
After that is done, go to the script itself (C#).
在完成此操作之后,请转到脚本本身(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.
在脚本任务完成后,来自源的所有列以及OutputCoulmns1-5都将可用,您可以做您必须做的事情。
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.
请问有不清楚的地方吗?
#2
3
You can use the Token expression to isolate strings delimited by well, delimiters.
您可以使用令牌表达式将字符串分隔开。
Use a derived column transformation
and something like this:
使用派生的列转换,如下所示:
TOKEN([Name_of_your_Column], "," , 1)
令牌([Name_of_your_Column]、”、“1)
Should give you "a"
应该给你“”
TOKEN([Name_of_your_Column], "," , 2)
”,“令牌([Name_of_your_Column],2)
Should give you "b"
应该给你“b”
You can also set up a simple transformation script component
. Use your "DATA" column as an input and add as many outputs as you need. Use the split method and you're set.
您还可以设置一个简单的转换脚本组件。使用“DATA”列作为输入,并添加所需的输出。使用split方法,然后设置。
string[] myNewColumns = inputColumn.split(",");
string[]myNewColumns = inputColumn.split(",");
#1
4
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)).
所以从源代码到脚本任务。然后在脚本任务中作为输入列,选择包含这些值的列(InputColumn1)。然后指定输出列(如果我是对的,我看到您有5个,所以指定5 (OutputColumn1 - 5))。
After that is done, go to the script itself (C#).
在完成此操作之后,请转到脚本本身(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.
在脚本任务完成后,来自源的所有列以及OutputCoulmns1-5都将可用,您可以做您必须做的事情。
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.
请问有不清楚的地方吗?
#2
3
You can use the Token expression to isolate strings delimited by well, delimiters.
您可以使用令牌表达式将字符串分隔开。
Use a derived column transformation
and something like this:
使用派生的列转换,如下所示:
TOKEN([Name_of_your_Column], "," , 1)
令牌([Name_of_your_Column]、”、“1)
Should give you "a"
应该给你“”
TOKEN([Name_of_your_Column], "," , 2)
”,“令牌([Name_of_your_Column],2)
Should give you "b"
应该给你“b”
You can also set up a simple transformation script component
. Use your "DATA" column as an input and add as many outputs as you need. Use the split method and you're set.
您还可以设置一个简单的转换脚本组件。使用“DATA”列作为输入,并添加所需的输出。使用split方法,然后设置。
string[] myNewColumns = inputColumn.split(",");
string[]myNewColumns = inputColumn.split(",");