Script Component in SSIS Implementation

Question

I have a code which is implemented in C# and When I try to Implement the same in SSIS component it is giving me error It would be of great help if any one could help me in this conversion.

Code is

using System;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Linq;

public class Test
{
    public static void Main()
    {
        List<string> thenames = new List<string>(new string[]
        {
            "XY YZ ZX Jr",
            "XY, Jr TR YZ",
            "XY Sr YZ.",
            "XY YZ Sr",
            "XY I YZ",
            "XY I YZ",
            "XY II, YZ",
            "XY YZ II",
            "XY YZ III.",
            "XY III. Yz."
        });

        var reg = new Regex(@"\w+", RegexOptions.IgnoreCase);
        var titles = new Regex(@"[JS]r|II?I?");

        foreach (String str in thenames)
        {
            var names = reg.Matches(str)
                .OfType<Match>()
                .Select(m => m.Value)
                .ToList();

            names.Reverse();
            List<string> output = new List<string>();
            foreach (String name in names)
            {
                Match title = titles.Match(name);
                if (title.Success)
                {
                    output.Insert(0, name);
                }
                else
                {
                    output.Add(name);
                }
            }
            output.Reverse();
            Console.WriteLine(string.Join(", ", output.ToArray()));

        }
    }
}

This code provides the below result

XY, YZ, ZX, Jr
XY, TR, YZ, Jr
XY, YZ, Sr
XY, YZ, Sr
XY, YZ, I
XY, YZ, I
XY, YZ, II
XY, YZ, II
XY, YZ, III
XY, Yz, III

I need to send the input from a table and get the required result. But not from the input so I tried writing in Script component of SSIS and below is the code and error I am Facing

using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Linq;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Text.RegularExpressions;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

        public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        var reg = new Regex(@"\w+", RegexOptions.IgnoreCase);
        var titles = new Regex(@"[JS]r|II?I?");

        foreach (String str in Row.XYZ)
        {
            var names = reg.Matches(str)
                .OfType<Match>()
                .Select(m => m.Value)
                .ToList();

            names.Reverse();
            List<string> output = new List<string>();
            foreach (String name in names)
            {
                Match title = titles.Match(name);
                if (title.Success)
                {
                    output.Insert(0, name);
                }
                else
                {
                    output.Add(name);
                }
            }
            output.Reverse();
            Console.WriteLine(string.Join(", ", output.ToArray()) + ".");

        }

    }

}

Error:

Cannot convert type 'char' to 'string'

Kyle Hale Kyle Hale · Accepted Answer · 2014-04-09T19:20:12

Your specific error is you are doing a foreach command against a string variable (Row.XYZ), where each iterated item returned is a char type and so you when you try to re-cast it as a String in the loop initializer you get the error you see.

Your more general problem is that your script task already is part of a loop. In your original code, you read in a List of Strings and so had to loop through them, but in SSIS, the loop is the data flow itself, and so each row is already passed into Input0_ProcessInputRow individually and so you don't need to do a foreach (Row.XYZ). Instead just do this:

var reg = new Regex(@"\w+", RegexOptions.IgnoreCase);
        var titles = new Regex(@"[JS]r|II?I?");


            var names = reg.Matches(Row.XYZ)
                .OfType<Match>()
                .Select(m => m.Value)
                .ToList();

            names.Reverse();
            List<string> output = new List<string>();
            foreach (String name in names)
            {
                Match title = titles.Match(name);
                if (title.Success)
                {
                    output.Insert(0, name);
                }
                else
                {
                    output.Add(name);
                }
            }
            output.Reverse();
           Row.YUX = string.Join(", ", output.ToArray()) + ".");

Script Component in SSIS Implementation

1 Answers