I have a requirement to split input data into multiple columns, but the data is delimited by multiple characters.
Whereas native C# supports string.Split("|--|") quite happily, I can't seem to get this to work in an SSIS script task as it will only accept a single character as the delimiter string.Split(',') for example.
Am I missing something or is this not available?
Thanks.
February 9, 2022 at 10:55 am
Single quotes are for chars. Use double quotes for string literals ... it should work.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Have you tried string.Split(new string[] { "|--|" }) ?
The split function is actually expecting an array of strings.
https://docs.microsoft.com/en-us/dotnet/api/system.string.split?view=netframework-4.5
February 9, 2022 at 3:01 pm
Thanks kaj; that did it.
Wasn't needed in "native" C# but clearly was in the Script Task.
February 9, 2022 at 3:05 pm
Here's an example of using a script component as a data source in a dataflow that outputs the delimited string as rows.
There doesn't appear to be an overload method that is simply split(String[]), I think you need to include the string split options, it appears maybe these are optional in regular .Net but required here perhaps.
SSIS does have a scaled down version of .Net. String.Split in an SSIS ScriptComponent only showed me 6 overload methods yet there are 10. Also Script Task and Script Component may differ, not sure. But the following works for a ScriptComponent. At least on my Win 10 laptop.
public override void CreateNewOutputRows()
{
var inputData = "Duck|--|Cat|--|Chicken|--|Cow|--|Pig|--|Dog|--|Horse";
string[] mySep = { "|--|" };
string[] inputSplit = inputData.Split(mySep,StringSplitOptions.None);
foreach(var animal in inputSplit)
{
Output0Buffer.AddRow();
Output0Buffer.MyData = animal;
}
}
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply