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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Derived Column Transformation My Expressin is not working

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server

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



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

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?



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

Working with In-Memory Open XML Documents (Office Talk Column)

Working with Open XML Documents without loading from a file or saving to a file is important when you build applications that work with Microsoft SharePoint Server 2010 or Microsoft ASP.NET Web applications.

wpf datagridview checkbox column selection not working fine.

Hi Experts,in my c#.net windows based wpf application i added  a datagridview.dynamically i added checkboxcolumn to that datagridview DataGridViewCheckBoxColumn cbCol = new DataGridViewCheckBoxColumn(); GridSource.Columns.Add(cbCol); and another column is string. to this column am hardcoding the values DataTable _datatable = new DataTable(); _datatable.Columns.Add("Name", typeof(string)); _datatable.Rows.Add("CSRs"); _datatable.Rows.Add("Customers"); GridSource.DataSource = _datatable; till here fine am getting two columns (checkbox and Name)when i am trying to check those checkboxes problem comming.am trying to get the count of the checked rows for that i used below code int cbcount = 0; foreach (DataGridViewRow _dgRow in GridSource.Rows) { if ((bool)_dgRow.Cells[0].Value) cbcount++; } cbcount will give the checked count.but the problem raising here if i check 2 rows it giving 1if i check 5 rows it giving 4if i check 1 row it giving me 0means am getting n-1 resultwhere i did wrong??

SSIS Changing Column Order during Transformation

First let me say, I really can't believe this chain of events myself--and they are happening to me. I am upgrading several DTS packages to SSIS on what will be my new production server.  These packages create tables, export them to a flat file, and ftp them off to other locations. What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file.  Then when my pickup/load routines run, the data is out of place and they fail. Can anyone please explain what is happening here with the mapping.  I have evaluated the table and the columns are in the order that I expect.  When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily. I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.  Thanks in advance for any help and/or information you can provide

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

Using derived column for calculations

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,  

hide gridview column using javascript. working in ie and firefox but In firefox, gridview data is sh


function EnableDisableGridViewColumn() {
               var AdvanceSearch = document.getElementById('<%= chkboxlist1.ClientID %>');
               var chkList = AdvanceSearch.getElementsByTagName("input");
               var dgTest = document.getElementById("gridview1")
               if (dgTest != null)
                   for (var i = 1; i < dgTest.rows.length; i++) {

                       if (chkList[0].checked == true && chkList[1].checked == true) {
                           dgTest.rows[0].cells[0].style.display = "block";

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?

CAML RichText site column provision not working properly


I have used the following CAML declaration in a feature to provision some site columns:

  DisplayName="Test Rich Text

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

Column ordering not working


Anyone have any idea why my column ordering would not be working?

It doesn't work in any of my site collections, on any library or list. 

Thanks in advance.

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?



Calculated Column...too many If Statements? Not working...


I’m trying to create a Calculated column in a Sharepoint list. I’m trying to use the statement below, but I get an error (The formula contains a syntax error or is not supported).


=IF([Piece Type]="Sign-Curvaceous","R CVS",IF([Piece Type]="Sign-Tag","R HG",IF([Piece Type]="Sign-Circle","R CIRC",IF([Piece Type]="Sign-Rectangle","R RS1",IF([Piece Type]="Sign-Pennant","R PEN",IF([Piece Type]="Frame-Fancy","R DD-CF",IF([Piece Type]="Frame-Rectangle","R RF1",IF([Piece Type]="Frame-Square","R SF1", IF([Piece Type]="Frame-Clip","R CFS1","0")))))))))


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