Wednesday, November 15, 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
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 
--------------------------------------------------------------------------------------------------------

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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog