MSBI (SSIS/SSRS/SSAS) Online Training

Monday, February 23, 2009

SSIS - Derived Column Transformation

The Derived Column transformation creates new column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input. The result can be added as a new column or inserted into an existing column as a replacement value. The Derived Column transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions.
You can use this transformation to perform the following tasks:
Concatenate data from different columns into a derived column. For example, you can combine values from the FirstName and LastName columns into a single derived column named FullName, by using the expression FirstName + " " + LastName.
Extract characters from string data by using functions such as SUBSTRING, and then store the result in a derived column. For example, you can extract a person's initial from the FirstName column, by using the expression SUBSTRING(FirstName,1,1).
Apply mathematical functions to numeric data and store the result in a derived column. For example, you can change the length and precision of a numeric column, SalesTax, to a number with two decimal places, by using the expression ROUND(SalesTax, 2).
Create expressions that compare input columns and variables. For example, you can compare the variable Version against the data in the column ProductVersion, and depending on the comparison result, use the value of either Version or ProductVersion, by using the expression ProductVersion == @Version? ProductVersion : @Version.
Extract parts of a datetime value. For example, you can use the GETDATE and DATEPART functions to extract the current year, by using the expression DATEPART("year",GETDATE()).
IntroductionIn 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.
[Click to see full-size]
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.
[Click to see full-size]
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.
[Click to see full-size]

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.
[Click to see full-size]
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 to see full-size]

Click OK then connect the output of the Derived Column to the Flat File Destination.
[Click to see full-size]

Testing the PackageLet’s see if the new column shows up in the new file. Run the package.
Once the package has completed running, go check the flat file to see if the derived column showed up in the transaction report.
More Expressions:-
(DT_STR,4,1252)DATEPART("yyyy",Derived.Date) + RIGHT("0" + (DT_STR,4,1252)DATEPART("mm",Derived.Date),2) + RIGHT("0" + (DT_STR,4,1252)DATEPART("dd",Derived.Date),2)
ROUND((DT_DECIMAL,2)DATEDIFF("mi",(DT_DBTIMESTAMP)Derived.Date,(DT_DBTIMESTAMP)CreatedDate) / (DT_DECIMAL,2)60,2)
DATEDIFF("HH",(DT_DBTIMESTAMP)Derived.Date,(DT_DBTIMESTAMP)GETDATE())
ROUND((DT_DECIMAL,2)Derived.Column / (DT_DECIMAL,2)60,2)

What have we learned?
How to configure a Derived Column component.How to use expressions to manipulate data inside of a package.

For More Details:-

http://www.programminghelp.com/database/sqlserver/sql-server-integration-services-derive-column/

1 comment:

Unknown said...

Hi,

i wanted to do MSBI training of classroom. please let me know whether you can provide it

Thanks
Niranjan
91-9886687656