click here to read: Split multi-value column into multiple records (Rows) using SSIS Part -1 (Using SQL script)
In this post, we will see how to load data using script component.
Taking data flow task.
Double click on the data flow task.
Taking source as the excel source.
When we are running this package then we will get the column value as log text.
I am taking data Conversion transformation.
Converting long text to string.
Click ok.
Now click on the script component.
Select the input column which you want to process.
Now select the Input and output.
Here we are seeing the list of input.
For the output, we need to create output column.
Add the output column.
After adding the output column we need to set the SynchronousInputID to none.
Now click on the Script.
Click on the Edit Script.
A C# window will be open. Here we need to write the code.
We write our c# code in Input0_ProcessInputRow Method.
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
// First we are converting the comma seperated list into a string array.
// You can change the comma if you are using an other seperator like | or ;
string[] Dist = Row.Districts.ToString().Split(new char[] { ',' }, StringSplitOptions.None);
// Counter var used the loop through the string array
int i = 0;
// Looping through string array with student names
while (i < Dist.Length)
{
// Start a new row in the output
Output0Buffer.AddRow();
// Pass through all columns that you need downstream the data flow
Output0Buffer.State = Row.State;
// This is the splitted column. Take the [n] element from the array
// and put it in the new column.
Output0Buffer.Districts = Dist[i];
// Increase counter to go the next value
i++;
}
}
|
Save and close this window.
Now I am taking destination as OLEDB Destination.
Creating the connection manager.
Here we need to map the columns.
Click ok.
See the records in database table.
Now I am running this package.
Package executed successfully.
See the records in the database.
See the records in the database.
