Split multi value column into multiple records (Rows) using SSIS part -2 (Using Script component)

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.

Creating the connection manager.

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.

Now I am taking script component transformation.

Select Transformation.

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.