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).
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