Wednesday, 15 November 2017

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
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..

FirstName :

SUBSTRING([EmpName],1,FINDSTRING( [EmpName],",",1)-1)
LastName :
SUBSTRING([EmpName],FINDSTRING( [EmpName],",",1)+1,FINDSTRING( [EmpName],";",1)-FINDSTRING( [EmpName],",",1)-1)
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 

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts