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.


  1. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

  2. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time sql server dba online training

  3. Thanks for sharing knowledge,Please do some more task.

  4. Great Content, congrats. I have a question would it still work if some first name records are only 1 string long ?