SSIS - OleDBDataAdapter Fill Method not Filling data table

Question

I'm trying to fill a data table with a object variable in SSIS but I'm having problem doing so. The first time a row passes the script component, the data table gets filled but, when the next rows passes through the script, the code doesn't fill the dataset and the whole script doesn't do its job correctly. Any help with this issue?

ScriptComponent Code
Main.cs
/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
///  string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
///  Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    OleDbDataAdapter sc = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    sc.Fill(dt, Variables.SpecialCharacter); //Problem occurs here, Fill method doesn't fill after first row is processed by the script.
    List<StringValidation> listObj = new List<StringValidation>();

    foreach (DataRow row in dt.Rows)
    {
        object[] array = row.ItemArray;
        StringValidation varObj = new StringValidation();
        varObj.SourceCharacter = array[0].ToString();
        varObj.TargetCode = array[1].ToString();
        listObj.Add(varObj);
    }

    StringValidation obj = new StringValidation();
    Row.ADDRL1 = obj.RemoveInvalidCharacter(Row.ADDRL1, listObj, "ADDRL1");
    Row.ADDRL2 = obj.RemoveInvalidCharacter(Row.ADDRL2, listObj, "ADDRL2");
}

StringValidation.cs
public class StringValidation
{
    public string SourceCharacter { get; set; }
    public string TargetCode { get; set; }

    public string RemoveInvalidCharacter(string text, List<StringValidation> listObj, string ColumnName)
    {
        foreach (StringValidation obj in listObj)
        {
            if (text.Contains(obj.SourceCharacter))
            {
                text = text.Replace(obj.SourceCharacter, obj.TargetCode);
            }
        }

        return text;
    }
}

Answer 1

Move code that only has to run once to PreExecute()

Try moving the DataTable filling into the PreExecute() method. It only needs to be loaded once, so any problems caused by multi-loading should go away if you do this. See my code (and comments) below.

/* Turn adapter and table into member variables */
OleDbDataAdapter sc = new OleDbDataAdapter();
DataTable dt = new DataTable();

public override void PreExecute()
{
    /* Fill data table in PreExecute(). Only needs to be loaded once. If it only works the first time this should fix it. */
    sc.Fill(dt, Variables.SpecialCharacter);
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    List<StringValidation> listObj = new List<StringValidation>();

    foreach (DataRow row in dt.Rows)
    {
        object[] array = row.ItemArray;
        StringValidation varObj = new StringValidation();
        varObj.SourceCharacter = array[0].ToString();
        varObj.TargetCode = array[1].ToString();
        listObj.Add(varObj);
    }

    StringValidation obj = new StringValidation();
    Row.ADDRL1 = obj.RemoveInvalidCharacter(Row.ADDRL1, listObj, "ADDRL1");
    Row.ADDRL2 = obj.RemoveInvalidCharacter(Row.ADDRL2, listObj, "ADDRL2");
}

And try this for performance improvement and cleaner code

You'd probably get even better performance if you moved all that into the PreExecute() section (see below). I also used Linq to simplify creating your List() object.

/* Don't forget to add using System.Linq; at the top of the page. */

List<StringValidation> stringValidations;
public override void PreExecute()
{
    OleDbDataAdapter sc = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    sc.Fill(dt, Variables.SpecialCharacter);
    stringValidations = (from scRow in dt.Rows
                         select new StringValidation
                         {
                             SourceCharacter = scRow[0].ToString(),
                             TargetCode = scRow[1].ToString()
                         }).ToList();
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    List<StringValidation> listObj = new List<StringValidation>();

    StringValidation obj = new StringValidation();
    Row.ADDRL1 = obj.RemoveInvalidCharacter(Row.ADDRL1, stringValidations, "ADDRL1");
    Row.ADDRL2 = obj.RemoveInvalidCharacter(Row.ADDRL2, stringValidations, "ADDRL2");
}