SQL Server Integration Services Derive Column

Introduction

In this article in an ongoing introductory series about SSIS, we will learn how to derive a column from existing data that is being processed. The Derived Column component is another component that is widely used in SSIS packages. It is versatile enough to accommodate a variety of needs.

Scenario

Since we are building on top of what we have already learned, we will use the previous article’s code to learn how to use the Derived Column component. As before, we have a list of credit card transactions and are dumping the transactions to a daily report file. Let’s say for instance, that this company the processes these transactions get a 5% processing fee of the credit card purchase. This processing fee needs to be in the daily report. This is where the Derived Column component comes into play

Implementation

Create a new package in the SSIS project. Copy the Data Flow component from the Export package into the Control of the new project. Change the name of the project to Derived. Go to the data flow tab and delete the connection between the Merge output and the Flat File Destination component. Rebuild the Vendor A and Vendor B connections then configure their respective Flat File Sources to use those connections. Next find the Derived Column component in the toolbox and drag it into the work area. Connect the output of the Merge to the Derived Column.

If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!

Double click the Derived Column component to configure the component’s properties. First we need to give the new column a name. Since it represents the processing charge we will call it ProcessingCharge. Tab to the next column and make sure that we are adding it as a new column. We can replace a column that we know we do not want by selecting the unwanted column in the drop down list. Tab over to the Expression column. The processing charge is going to be a compute column based on the amount of the credit card purchase. So, in the upper left hand box, expand the list of columns and drag the Amount column down to the Expression field in the table.

We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!

As you can see from the right hand box there are a few ways to manipulate data. Expressions are used in several different spots in SSIS to calculate and manipulate data as it flows through the SSIS package. Since the Amount data type is coming in as string we need to convert it to decimal so we can perform some basic math on it. So expand the Type Casts tree and find the decimal data type, (DT_Decimal), and drag it in front of the Amount column in the Expression field in the table and tab over the Data Type column.

The text is red since the expression results in an error. Inside the decimal type cast, there is a field called scale that is inside a set of >. This needs to be filled out which in this case it will be set to 1. Now that the Amount column is in a data type we can perform some math on, we will then multiple it by .05. You can either type in * or you can expand the Operators tree and drag the appropriate operator to the Expression field.

Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!

Click OK. Since the output of the data, the structure of the output file needs to be changed as well. Use the file included in the source or you can manually add the ProcessingCharge column at the end of the first line in the file. Create a flat file connection in Connection Manager to be used with altered file. Now configure the Flat File Destination component to use the new connection. Go to the Mappings tab to make sure that the data will be processed into the file correctly.

Click OK then connect the output of the Derived Column to the Flat File Destination.

Testing the Package

Let’s see if the new column shows up in the new file. Run the package.

We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

Once the package has completed running, go check the flat file to see if the derived column showed up in the transaction report.

What have we learned?

How to configure a Derived Column component.
How to use expressions to manipulate data inside of a package.

Attachments