.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

Using derived column for calculations

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
Hi Guys, Im trying to calculate a value using a derived column. I have this following recordset in my data flow. C1 C2 ITM 88 ITM 10 TX 52.45 DC 92.18 I have this formula: 1. DC = (DC- TX) * 1.12 So the result would be like this. C1 C2 ITM         88.00 ITM         10.00 TX         52.45 DC         44.50 Thanks,  

View Complete Post

More Related Resource Links

How to recover from Derived Column Transformation Editor corrupting table metadata?

Hi there, In attempting to use the DCTE to replace the value in a field with a trimmed version (SSN = trim(SSN)), it seems that my meta-data has become corrupted by the Derived Column drop-down list. As a result, #1: I no longer see my incoming SSN field in the "columns" tree, and any reference to it is deemed "invalid", even though it's value does make it out of the Data Transformation process. How can I get back the reference to this field without redoing this entire task? And, #2: in the process, DCTE created new columns with "SSN" prefixed by the task name, such as "trim character fields.SSN". How can I delete these? It seems that one slip of the mouse in this form can lead to irreversible corruption of the meta-data, which the "debugger" then references and uses to "invalidate" subsequent work. I have tried everything I can think of to refresh this, including using the "Advanced Editor" and reloading the entire package. Any ideas? Thanks, Karl Kaiser

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

Derived Column Transformation My Expressin is not working


Hi Folks

My expression is not working -its always returning "N" if I find the word AIWORKS I want to return "Y" otherwise if not found "N"


finally if i want to test a whole lot of conditions why is this come up in red
COMPANY == SUBSTRING(LTRIM("AIWORKS"),1,7) ? "Y" : "N" ||COMPANY == SUBSTRING(LTRIM("Google"),1,6) ? "Y" : "N

thanks in advance



derived column


I am in the process of placing the data in a file into a table.
There is a field called MyDate in the file which is coming in as ddmmyyyy
Now I would like to place this into the MyDate field of my table1. This field is of type smalldatetime in the table.
I am using a derived column and in the expression I have place the following code but it shows it as red.
What is wrong please?

Derived Column & Lookup


I have a derived column (MyNewID) that strips off the data I require like this:


This leaves me with a string that would look similar to 12345 (or whatever my ID is). What I need to do now is use MyNewID column to perform a lookup from a another table. I have tried using the the lookup, but can;t find a way to passw my new derived column's value into my Lookup's SQL query (which looks something like this:

 select Field1, Field2
 from tblSales s
 where s.SaleID = (this is where my parameter/derived column needs to be)

Is there a way to pass the value through somehow?  Am I using the right method to do this?

How to add another column to sum/average/count/or any possible calculations to those pivoted columns


Hi All,

I am currently using this Dynamic Pivot Table Queries from this link:


I manage to get the result i required but i need to add further column to it. I have a table such as this.

Name  |   Item   |   Points

Eddy       A             3

Eddy       B             4

John       A             2


I could get the result such as this from the above link after pivot.

Name  |  A   |    B

Eddy      3         4

John       2         null

How do i make it to become like this? Adding a column average so the final result will look like this.

Name | Average |  A  |  B

Eddy     3.5           3      4

John        1          

SUBSTRING within OLE DB Source sql vs Derived Column


One requirement of a project I'm working on is that the value within a column of my source be truncated to 30 characters.  The source column is currently a length of 50.  The previous developer was selecting the column, then downstream, used a derived column to substring the value into a new column.  The data set within this package is quite large and I'm attempting to clean it up.  The full 50 length value is never used (other than for the source of the 30 length derived column).

The question - is it good, bad, or indifferent practice to do this SUBSTRING from within the OLE DB Source SQL Command right up front, rather than passing the entire length down to a derived column?  I would assume, although maybe not eloquent possibly lending itself to more difficult debugging, it would cut down on the amount of data in the pipeline and negate the need for a derived column transformation entirely, which I assume would cut down on process time.

I realize both would work - just curious about best practice/performance impacts.



derived column


in my source i have month field and tear  and day fields but i added fulldate that is required  and i have to insert data to the respected rows based on the respected fields columns

y        m   d         fulldate

2006 08   6     2006-08-6

2007 09  18     2007-09-18

SSIS - Derived Column Error Rows will not redirect to table


I am new to SSIS and I have created a package that generally successfully takes a Flat File Source, runs it through a Derived Column transformation and then pushes the data into a SQL Server Database.

I have a Derived Column Error Output set up, but it is not working.  If the data has an error the entire package fails, not just the specific row.  I have tested the package to load successfully and then manually gone into the flat file and changed the data to purposely create an error in one row. Having cleared the load table I rerun the package and it fails entirely rather than redirecting the one bad row.

In the Derived Column Transformation Editor I set the Error and Truncation values to Redirect Row for all the columns.

In SQL Destination Editor for the bad rows I am only mapping the ErrorCode abnd ErrorColumn values to the destination table fields of the same name.  Could this be the problem? Should I map all the fields?  I would like to map all the source (derived) fields into a single text field in the destination table if possible.

The Advanced settings have only the Table Lock and Check constraints boxes checked.  Timeout is 30.

I am not really sure how to set the Advanced Editor properties.  I have left them at the default values.  Any suggestions here?



Derived Column Transform with average value (from SQL Server to SQL Server)


Hello everyone,

sorry if the following appears like a total newbie question, but I'm taking my first college course using MS SQL Server & SSIS, so please bear with me :)

I'm currently working on an assignment and face a couple (maybe mundane) problems:

  1. I'm trying to create a new (bit) column which should compare sales to an average and if the sales exceed the average the bit should be flipped, i.e. put a "1" into the database. The problem here: the average should be calculated from the same dataset and I'm trying to be smart and have it calculated with every new run of the data flow. But the derived column transform only takes one input, how can I get the average into it? I tried to have an aggregate transform and have that assigned to a variable which I'd have used in the expression statement, but I couldn't figure out how to do that - like I said, a I'm a newbie here.. :(
  2. Something similar, I'm trying to transform a gender code ("f"/"m") in the database to a bit value. The transformation itself I managed to get done (Excel output worked out fine), but I'm having trouble writing it back into the database table. In plain SQL (which I used before) I'd just write an UPDATE statement, but one point of the assignment is to use SSIS for all the tasks. When trying to use the OLE DB desti

derived column transformation


Hi frdz, one small question. i am just adding new column called CallAnswer in derived transformation task in my package and it would have yes and no records. what do i use the expression for that column as its a new column?



SSIS Derived Column and Data Conversion Task


I am passing down a variable, filename, as  string and then using Derived Column to pass it down to the destination. The filename is numeric (date) like such: 20101010. So, I need to convert the filename into a date formmat before I pass it to the destination table. Therefor, I am using a data conversion and converting it to a Date format. I tried using both database date or the date format and it keeps failing.

Here is what the failure report:

[Data Conversion [42297]] Error: Data conversion failed while converting column "DateAdded" (29685) to column "Copy of DateAdded" (42310).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".  

Any ideas to resolve this issue?



Call a SQL Server function(cleans html tags) on a derived column in SSIS


I have a task to move table having "text" data type for a column from SQL Server to CSV file so while transfering the data using SSIS I want to perform Cleanup on this column such as removing HTML tags, White spaces etc and then transfer it. Note: I dont have any option to store the table in the staging database as I will be running it on the production server directly.

Can any one help me accomplish this task???





How can i convert Integer to String in SSIS Derived column.


i have gender column with 0,1 values

i`ve to convert it into 0=male and 1=female,i tried this


BUT it is giving error

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Derived Column [46]]: The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Error at Data Flow Task [Derived Column [46]]: Attempt to set the result type of binary operation "Gender == 0" failed with error code 0xC0047080.

Error at Data Flow Task [Derived Column [46]]: Computing the expression "[Gender] ==0?"Male":[Gender]==1?"Female":"NA"" failed with error code 0xC0047084. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

Error at Data Flow Task [Derived Column [46]]: The expression "[Gender] ==0?"Male":[Gender]==1?"Female":"NA"" on "input column "Gender" (114)" is not valid.

Error at Data Flow Task [Deri

Derived column fails when mapping


Hi All,

I have my OLEDB source and I have attached it to a "Derived Column" and then attached this to "Import Column". I intorduced the Derived Column because linking straight to the Import column from the source errored with unsupported types when trying to map.

I have altered 1 column without problem, but I have a second column where the source type is double-precision float [DT_R8]

I have derived the column and told it to replace. The expression I have used is: (DT_STR,15,1252)(col1)

No complaints from the Derived Column Transformation editor.

When I go to the "Import Column" and select my derived col1, it still says it's an unsupported data type and must be either DT_STR or DT_WSTR ???? 

Derived column TRANSFORMATION Parsed Error


Hi i am getting following error message while executing a package that 
updates the column value using a derived column transformation..
[TRR_FE_Parse_File [271]] Error: The "component "TRR_FE_Parse_File" (271)" failed because truncation occurred, and the truncation row disposition on "output column "SrcFileName" (32263)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "TRR_FE_Parse_File" (271) failed with error code 0xC020902A. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
Error: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC020902A.  There may be error messages posted before this with more information on why the thread h

Calculations on specific cells of column group in tablix


i have a tablix which shows sales for current period,year-to-date,last year period, and last year year-to-date.

sales         P4   YTD  LastYearP4 LastYearYTD 

x             100   400    85               230

y             23    99     19                 82

TOTAL     123  499    104              312

i want to have an index column based on formula current period/last year period. Here periods are part of column group.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend