SSIS C#: Read two variables, join them in a two dimensional array, assign to an object variable

SSIS C#: Read two variables, join them in a two-dimensional array, assign to an object variable.

–write DataTable to an object variable

Part 1 plan:

  1. I will receive value in project parameter. For example: SSIS Project variable CCGCode=0A;1D;2J, SSIS Project variable PCTCode=0A;1D;2J
  2. Read the multiple values from the project parameter separated by semicolon;
  3. Split the values by semicolon then insert into a DataTable
  4. Assign the DataTable to an Object Variable

Part 2 plan:

  1. Foreach ADO Enumerator
    1. Collection: Read from the Object Variable
    2. Map the values to a string variable
  2. Use the values to in the task

public void Main()
{

DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
dt.Columns.Add(“CCGCode”, typeof(string));
dt.Columns.Add(“PCTCode”, typeof(string));

string[] ccgCodes, pctCodes;
ccgCodes = Dts.Variables[“$Project::CCGCode”].Value.ToString().Split(‘;’);
pctCodes = Dts.Variables[“$Project::PCTCode”].Value.ToString().Split(‘;’);
for (int i = 0; i < ccgCodes.Length; i++)
{
DataRow dr = dt.NewRow();

//without using the string.IsNullOrEmpty() check it fails, even the input does not have any null values.
dr[“CCGCode”] = string.IsNullOrEmpty(ccgCodes[i])? “NUL” : ccgCodes[i];
dr[“PCTCode”] = string.IsNullOrEmpty(pctCodes[i]) ? “NUL” : pctCodes[i];
dt.Rows.Add(dr);
//MessageBox.Show(dr[“CCGCode”].ToString());
//MessageBox.Show(dr[“PCTCode”].ToString());
//codes.Add(new CCGPCTCodes(ccgCode[i], pctCode[i]));
}

try
{
Dts.Variables[“User::objCodes”].Value = dt;
}
catch (Exception e)
{
Dts.Events.FireError(0, null, e.Message, null, 0);
}
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s