29 Dec’17

BIHow 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.

Excel Source
Excel Source

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.

Written by

Simran Monteiro

Trainee Software Engineer

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Commands to Import .bacpac file to D3FOE SQL Server

    14 May’ 2018

    Introduction: This blog article will explain how to import a .bacpac file to Microsoft SQL Server which is created from ...

    Read more
  • [Solved] – Backspace not working in HTML Web Resources

    11 May’ 2018

    Issue: When we use a HTML web resource in Dynamics 365 CRM forms or even as a separate navigation, we sometimes face is...

    Read more
  • Dynamics 365 Tip: Sending Emails using Unified Interface

    10 May’ 2018

    Introduction: The unified client which was released D365 V9 had a very refreshing UI and lots of features. One of the b...

    Read more
  • Filtering Records on BPF unlike on Form – D365

    10 May’ 2018

    Introduction: Working with D365 is best when you’re trying to use as much OOB stuff as possible. Simplest configurati...

    Read more
  • Filter records in gallery control based on value selected in other gallery control in PowerApps.

    10 May’ 2018

    Introduction: This blog explains how to Filter records in gallery control based on value selected in other gallery cont...

    Read more