SSIS Derived Column Transformation

When you create an ETL process, sometimes the source data is not in the format that you need to load into the database.  Below is an example of using a derived column to format the data so it fits into the database the way you require.

Here is what our database structure looks like.  The customer name field has both first name and last name in the same field.

dc1

 

If we look at the source data we need to import into our table, the name fields are split out into first and last.  We need to combine them during the load so the information fits into our table.

dc2

 

To combine the values goto your SSIS toolbox and drag over the Derived Column component.

dc3

 

Double click the derived column component and it opens the transformation editor.

dc4

It looks confusing, but we just need to do the following steps.  On the left pane, click the columns item, and it shows the columns that are coming over from the csv load process.  Then select firstname under columns and drag it into the Expression box in the bottom pane.  It is hard to show in pictures, but it will drag.

dc5

 

Now we need to concatenate the lastname into the Expression.  We can use SQL Server formatting to do this.  Below I add a:  + ” ” + and then drag in the last name column.  This will display the first name with a space and then lastname.

dc6

 

Now we add a destination control using the SSIS toolbox.  Double click it and it brings up the editor.  Below I selected the table I want the csv file to be loaded to.  After that click the Mappings tab in the left pane.

dc7

 

This allows you to map which fields from the import process goto the correct fields in the table of the database.  It usually intelligently maps the fields automatically if the field names match in the source file and the destination.  The highlighted “ignore” means that field will not be imported because it could not find a match of the names.  If we click that box, it will display a dropdown with the derived column that combined the fields from above.

dc8

 

Below we selected the field “CombinedName” that we created above.

dc9

 

Now before running the task, I like to enable the data viewer so you can see what the data will look like before it gets imported.  You do that by right clicking the blue line between the derived column task and the destination table and selecting enable data viewer.

dc10

That will put a little magnifying glass on the blue line.

dc11

 

Now when we run the task, a box will pop up showing you the columns of data that will get processed.  In yellow below, you can our CustomerName column.  It all looks good.  Press the green arrow on the left to let the job continue.

dc12

 

We got all green check marks, so the process did not have any errors.

dc13

 

If we look in the database, we can see our data got imported correctly with the first and last names combined.

dc14

 

You can use the Derived Column component to do all kinds of transformations.  To read more about it please follow this link:  https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/derived-column-transformation?view=sql-server-2017

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s