Split a single column of data with comma delimiters into multiple columns in SSIS
Solution 1
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.
Solution 2
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)
Should give you "a"
TOKEN([Name_of_your_Column], "," , 2)
Should give you "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.
string[] myNewColumns = inputColumn.split(",");
VishalJ
Updated on July 09, 2022Comments
-
VishalJ almost 2 years
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
-
VishalJ over 10 yearsI'm guessing this is an expression in 2012, I'm actually using 2008, sorry for the confusion
-
TsSkTo over 10 yearsIt wouldn't have mattered.. I had no clue SQL 2008 didn't have these expressions.
-
criticalfix over 10 yearsTOKEN is quite useful in SQL Server 2012. Note there is an interesting problem with token counting when your delimited string includes some empty fields.
-
VishalJ over 10 yearsI followed your instructions, but it seems to have brought the InputColumn1 as a Blob so doesn't allow the Split method. I tried using the ToString().Split(',') (get a Index was outside the bounds of the array error) but that didnt work either. i went into the BufferWrapper.cs file and saw that the InputColumn1 was of type Blob and was returning a BlobColumn, tried replacing the type with string but then get a Unable to cast object of type 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn' to type 'System.String' error .Could it be to do with that?
-
Inus C over 10 yearsTo be honest, I have never worked with a Blob. Can you not change that in your source task? Change the type it is received as?
-
TsSkTo over 10 yearsDon't edit BufferWrapper.cs, it's auto generated. You can only modify the datatypes for the inputs in the script component editor. What you actually need is a
data conversion
component to handle your blob. Place it before your script componenet and convert the column to string -
VishalJ over 10 yearsThanks, Changed the source from DT_TEXT to DT_STR and worked like a charm :)
-
Inus C over 10 yearsHappy to hear that. Glad I could point in the right direction. Along with TsSkTo :)