Problem: Your source data contains a column that should be split in two. In this case there is a name column in an Excel file that contains both the first and last name. The destination database has the first and last name as separate columns.
Solution: Use the script the component and split apart the column data into a string array.
Step I: In a Data Flow add an Excel source and then add a Script Component with the component type “Transformation.” Add a First Name, Middle Name and Last Name as column outputs. Be sure to add the name column from the source data as an input column.
Step II: Open the script editing window and edit the ProcessInputRow method. In this example were going to assume that each name is separated by a space. Using the String.Split() method, break up the name column into a string array. If the array length is 3, then we have a first, last and middle name. If the array length is only 2, then only the first and last name is present.
Step III: Assign the output columns with the appropriate item from the index.
{
public override void InputCustomers_ProcessInputRow(InputCustomersBuffer Row)
{
var names = Row.Name.Split(‘ ‘);
//if lenght is two, not middle skip the middle name
if (names.Length == 2)
{
Row.FirstName = names[0];
Row.LastName = names[1];
}
else if (names.Length == 3)
{
Row.FirstName = names[0];
Row.MiddleName = names[1];
Row.LastName = names[2];
}
}
}
We now have three columns ready to be passed to the destination data source.
