SSIS - How To Use FindString Function in Derived Column Transformation in SSIS Package


A friend of mine got this task to do, He is getting an excel file in which he has a single column for name. The name column consist of First Name and Last Name. He needs to split/break this column into First Name and Last Name. The problem is that the space between the First Name and Last Name is not same.


To solve this problem, We will be using Derived Column Transformation. Inside the Derived column Transformation, we will be FindString() Function to split this first Name and Last Name.

FindString()  Returns us index position of Character or String in given Data Column.

FindString ( DataColumn,SearchString, Occurance)

Step 1: 

Let's create a sample text file. Instead of creating an Excel file. I have created .txt file. You can have anything that you like as source. Here is the data that I used for this NameColumn.

Aamir Shahzad
Raza      Ali
Puneet                    Rana
Sukhjeet    Singh
Andrew   Jason

As you can see that the space between each name is not same.

Step 2: 

Create an SSIS Package, Inside your SSIS Package, Drag Data Flow Task and then create connection to source file by using Flat File Source as shown below.
Fig 1: Create Flat File Connection for Split Column Data into Multiple Columns in SSIS Package

Fig 2: Flat File Connection Manager in SSIS Package for Break Column Data into Multiple Columns

Step 3: 

Drag Derived Column Transformation and connect Flat File Source to it as shown below. 
Fig 2: Derived Column Transformation to Split Column Data into Multiple Columns in SSIS Pacakge

Here are the expressions those I used in Derived Column.

First Name ==>> SUBSTRING(TRIM(NameColumn),1,FINDSTRING(NameColumn," ",1))
In above expressions, I found the very first blank space by using FindString function. It returns the index number or position number of character of string. Then I use the SUBSTRING() function to start from first character and end to very first space( that we got by using FINDSTRING() function).

Last Name ==>> TRIM(SUBSTRING(TRIM(NameColumn),FINDSTRING(NameColumn," ",1),LEN(NameColumn)))

To Find the Last Name, we used the very first space index number ( that is returned by FINDSTRING()) Function) as starting point and then LEN() of Column as end position for SUBSTRING() Function.

There could be more complicated scenarios in which you might have to change the script little bit to handle situations according to the provided data.

Step 4:

Let's put a dummy transformation ( in our case we use Multicast), Add Data Viewer and run the SSIS Package to see if we are getting out required results.
Fig 4: Breaking Column Values into Multiple Values in SSIS By using FINDSTRING Function.

As we can see that First Name and Last Name columns are successfully derived from NameColumn by using FindString() and Substring() function in Derived column Transformation in SSIS.