How to Split Full Address into Multiple Columns By using Derived Column Transformation in SSIS Package - SSIS Tutorial

I was reading a questions in MSDN SSIS Forum, How to convert Full Address that is separated by "," into multiple columns in SSIS Package.

There are multiple ways to handle this situation, we can use Script Component to handle this or if we would like to do we can use Derived Column Transformation.

Let's say we have a source Excel file with below data,In this example it has only one column call Full Address, In real time scenario you might would have more than one column.

FullAddress
720 Red Oak,Charlotte,NC,28200
110 River Side,New York City,NY,7800
500 Rio Grand,Rio Rancho,NM,2100

Fig 1: Excel Source File with one column Data

Step 1:
Create an SSIS Package by using BIDS or SSDT. I have SQL Server Data Tools,

Step 2:
Once the SSIS Package is created in Project, Bring the Data Flow Task as we will be reading the data from Excel file by using Excel Source.

Step 3:
Drag Excel Source from Sources in Data Flow Task Pane and then configure as shown below.

Fig 2: Configure Excel Source in SSIS Package to Read Data from Excel Source

Step 4:
Bring the Derived Column Transformation and Connect Excel Source to it. Double Click on Derived Column Transformation and Write below Expressions.

If you are interested to learn more about how FindString Function works, please visit Link



StreetAddress                          
(DT_STR,50,1252)SUBSTRING(FullAddress,1,FINDSTRING(FullAddress,",",1) - 1)

City
SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",1) + 1,FINDSTRING(FullAddress,",",2) - FINDSTRING(FullAddress,",",1) - 1)

State
SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",2) + 1,FINDSTRING(FullAddress,",",3) - FINDSTRING(FullAddress,",",2) - 1)

Zip
SUBSTRING(FullAddress,FINDSTRING(FullAddress,",",3) + 1,LEN(FullAddress) - FINDSTRING(FullAddress,",",3) + 1)


If you wish to like to change the datatype you can change it here as well by Cast to required data type. Let's say you want to convert to VARCHAR , in front of express use (DT_STR,50,1252) as I did for StreetAddress column to convert to VARCHAR(50).

Fig 3: Write Expression to Split Single Column into Multiple Columns in Derived Column Transformation in SSIS Package

Now you can connect Derive Column Transformation to your final Destination. I have connected to Multicast Transformation and added Data Viewer so I can show you the final output.

Fig 4: Split single column to Multiple Columns in SSIS Package

Video Demo: How to Split Single Column into Multiple Columns in SSIS Package

Check out our other related posts/videos on Excel Source And Excel Destination

• How to Create Excel file with Date-time on Each Package Execution in SSIS Package?
• How to Load Multiple Sheets to a SQL Server Table in SSIS Package?
• How to Load Data Excel File to SQL Server Table and Solve Data Conversion Issues?
• How to Load Multiple Sheets From Multiple Excel Files to Different Tables in SSIS Package?
• How to Load Data to Pre-Formatted Excel Sheet (Excel Report) in SSIS Package?
• How to Read Alpha Numeric Data from Excel Source File