How to split an ‘Address’ column into multiple columns in SSIS

Introduction:

We can split an Address column into multiple columns for Flat Number, Building, Road, City etc. in order to make it easier to process the data.

In order to do this, we can use the Derived Column Transformation in SSIS.

Steps:

In the screenshot below, you can see the Address column has an aggregated address for each employee.

In your SSIS Package,  bring a Data Flow Task. Configure you Excel Source in Connection Manager and select the sheet you want the data from.

Check in the Columns Tab to verify if the Columns are correct.

Now drag the  Derived Column Transformation and connect the Excel Source to it. Double click the Derived Column Block and configure it as shown in the screenshot below.

Flat: SUBSTRING([Col 2],1,FINDSTRING([Col 2],”,”,1) – 1)

Building: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,1) + 1,((FINDSTRING([Col 2],”,”,2) – FINDSTRING([Col 2],”,”,1)) – 1))

Road: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,2) + 1,((FINDSTRING([Col 2],”,”,3) – FINDSTRING([Col 2],”,”,2)) – 1))

Area: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,3) + 1,((FINDSTRING([Col 2],”,”,4) – FINDSTRING([Col 2],”,”,3)) – 1))

City: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,4) + 1,((FINDSTRING([Col 2],”,”,5) – FINDSTRING([Col 2],”,”,4)) – 1))

PIN: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,5) + 1,((LEN([Col 2]) – FINDSTRING([Col 2],”,”,5))))

Once the configuration is done, connect Derived Column Transformation to you Target Block.

Configure your Target Block to your Destination as shown below and check the Mappings to ensure the correct Source and Target columns are mapped.

This is what your Data Flow should look like:

Click on ‘Start’ when done. Once the execution is complete, check your Target Database for the desired output.

Conclusion:

This can be a requirement in many scenarios and by following the steps mentioned above, we can achieve the desired output.