SSIS Package to Split a Column of a Flat File Source into Multiple Columns and Load to SQL Server Destination

How to Split a Column into Multiple Columns from Flat File and Load to SQL Server
Scenario:
Suppose we have the source data in Flat File as follows : 
EmpId  EmpName                          Dept    BasicSal
123       Michael,Edward;Smith        BI&A    750000
124       John,Francis;Kennedy        F&A      55000
125       Elizabeth,Rose;Water         R&D     95000

here, in the above data EmpName consists the FirstName , LastName ; MiddleName, which we need to split before loading to the Destination Table :

Now we can design the SSIS Package to Split the column EmpName to 3 columns as follows:

Phase -I : Create Connections for Source Flat file and Destination Table :

EmpSrc_FlatFile - This is the connection to the Flat File Source

EmpDB - This is the connection to the Destination Table

Phase -II : Design the Data Flow:

1. Add the Data Flow task to Control Flow 

2. Next add the Flat Source to DataFlow and set connection properties

Here we simply add the FlatFile source and select the connection. Nothing special in the properties setup.

3. Next add the Derived Column Transformation to Split the EmpName Column

In the derived Column Transformation, we can derive FirstName, LastName and MiddleName from EmpName as follows..

here,
FirstName :
SUBSTRING([EmpName],1,FINDSTRING( [EmpName],",",1)-1)

LastName :
SUBSTRING([EmpName],FINDSTRING( [EmpName],",",1)+1,FINDSTRING( [EmpName],";",1)-FINDSTRING( [EmpName],",",1)-1)

MiddleName:
SUBSTRING([EmpName],FINDSTRING( [EmpName],";",1)+1,LEN( [EmpName])-FINDSTRING( [EmpName],",",1)-1)

4. Next do the TypeConversion with Data Conversion Transformation

5. Finally map the required Source columns to OLE DB Destination Table

------------------------------------------------------------------------------------------------
Output :

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------