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.