Scenario:
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.
Solution:
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.
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.
Your blog is awesome with some unique content in it. Thanks for taking time to share.
ReplyDeleteSpoken English Classes in Chennai
Spoken English in Chennai
Top 10 Spoken English Classes in Chennai
Best IELTS Coaching in Chennai
IELTS Coaching Centre in Chennai
English Classes in Mumbai
English Speaking Classes in Mumbai
Best IELTS Coaching in Mumbai
IELTS Coaching in Mumbai
Spoken English Class in Anna Nagar
Thanks for sharing knowledge,Please do some more task.
ReplyDeleteGreat Content, congrats. I have a question would it still work if some first name records are only 1 string long ?
ReplyDeletethanks
Aivivu - đại lý chuyên vé máy bay trong nước và quốc tế
ReplyDeletevé máy bay đi Mỹ giá bao nhiêu
vé máy bay từ mỹ về việt nam hãng ana
vé máy bay từ đức về việt nam giá rẻ
vé máy bay giá rẻ từ nga về việt nam
khi nào có chuyến bay từ anh về việt nam
chuyến bay từ pháp về việt nam hôm nay
khách sạn cách ly ở việt nam
chuyen bay chuyen gia trung quoc