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:
5. Finally map the required Source columns to OLE DB Destination Table
------------------------------------------------------------------------------------------------
Output :
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)
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,FINDSTRING( [EmpName],";",1)-FINDSTRING( [EmpName],",",1)-1)
MiddleName:
SUBSTRING([EmpName],FINDSTRING( [EmpName],";",1)+1,LEN( [EmpName])-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 genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.