Thursday 1 September 2016

Read and Write Object Variable in SSIS


Please consider the below scenario:



Aim is to load the excel sheet data into RDBMS table only through SSIS.
(i.e. There is a limitation to not create any tables for separating comma separated values).

1) Create two variables(User::V_SOURCE_DATA,User::V_DEST_DATA) of object type in SSIS.

2) I directed all the rows read from excel sheet into a Record Set Destination.
So now i captured all my source data in the object named User::V_SOURCE_DATA.

3) Now i am going to create a simple C# script which reads data from object(User::V_SOURCE_DATA) and separates HOBBIES column using ","
and populates data into object(User::V_DEST_DATA).

using System.Data.OleDb;

// Create data table to hold data of output format with two columns(EMP_ID,HOBBIES).

DataTable dest= new DataTable();
dest.Clear();
dest.Columns.Add("EMP_ID");
dest.Columns.Add("HOBBIES");

// Read Source Data

DataTable dt= new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["User::V_SOURCE_DATA"].Value);

string emp_id = "";
string hobbies_str = "";

foreach (DataRow row in dt.Rows)
{
emp_id = row[0].ToString();
hobbies_str = row[1].ToString();

// MessageBox.Show("EMP_ID = " + emp_id + "; HOBBIES = " + hobbies_str);

string[] hobbies_str_split = hobbies_str.Split(',');

foreach (string item in hobbies_str_split)
{
// MessageBox.Show("EMP_ID = " + emp_id + "; HOBBIES_SPLIT_STR = " + item);
  // Fill new DataTable with Fund_ID
DataRow _fill_data = dest.NewRow();
_fill_data["EMP_ID"] = emp_id;
_fill_data["HOBBIES"] = item;
dest.Rows.Add(_fill_data);
  
}
   
}

Dts.Variables["User::V_DEST_DATA"].Value = dest;

4) Now the output data available in the object named User::V_DEST_DATA.

5) Atlast data from object read using FOR_EACH_LOOP container and passed into destination using EXECUTE SQL TASK Insert Script.

Note: When reading from object, map the output column values to object type variable else datatype mismatch problem will arise.

INSERT INTO EMP_HOBBY(EMP_ID,HOBBY) VALUES (?,?);

No comments:

Post a Comment