.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

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

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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



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

otput column in script transformation editor



Using script transformation editor I'm trying to generate an output column to use it again in the data flow, I can create it using the editor but when I'm going to use it to store data (string type) in C# code I only find a Buffer type object. Can you please help me with this?




Display column from child table. Possible ?



I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

DataGrid: Tailor Your DataGrid Apps Using Table Style and Custom Column Style Objects


One of the most enduring challenges in writing user interfaces is figuring out how to display large amounts of data efficiently and intuitively without bewildering the user. The problem becomes particularly thorny when the interface must reflect hierarchical relationships within the data that the user needs to modify. The Windows Forms DataGrid control gives developers a powerful and flexible tool to meet this challenge. This article explains its basic operations and shows how to extend the DataGrid to display columns of data in an application-appropriate manner.

Kristy Saunders

MSDN Magazine August 2003

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

SQLCE table column DefaultValues don't show up in XSD Dataset designer

Using Visual Studio 2008 with SQL CE 3.5, I notice that default values in the creation scripts for the database tables are not reflected in the dataset designer XSD file.  For example, the following SQL script creates the non-nullable table column names "Content" with a default value of 'Image':    "Content" nchar(20) NOT NULL DEFAULT 'Image',But in the column properties of the dataset designer (XSD) panel, this column correctly shows up as non-nullable, but with a DBnull default value as follows:     Name: Content    Allow DBnull: False    DefaultValue: <DBnull>Am I missing something somewhere, or is this a VS bug?  Also, how do I get the XSD file to regenerate after schema changes in the database?  Sqlmetal doesn't do it.Thanks,    -BGood

Fetch Identity column just after inserting a row in table

Hi, Please help me with this question. I have a table and I insert a row into it. How can i select the latest inserted row? Can the 'inserted table' keyword be used outside trigger? ( I mean can we use it in above scenario?? If yes how)   Thanks in advance Tiya

can alias in select be used for selecting other column in that table?

Hi All,I want to use an alias name in a select clause to select other column in that table? select   top 1 (   case when CreatedByName <> '' then 'yy'         else 'xx' end) as filName, (filName + 'xx')from Order       But it throws error like " Invalid column name 'fileName'."Could you please help me out?

How to get a Table name from the column Value in Sql Server?

Hi All,I have a number of tables in the database and i have a column value as "abc" coming from one of the tables in the database,Now i need to find the table name from where this column value is coming?

Is it suggested to have extra column on the SSISConfigurations SQL Configuration table

Hello, I have all my configurations are saved in SQL Server table. SO I just wanted to know if it is advisible to have extra column on that table? Thanks, Prabhat

Provision DocumentLibrary with Managed Metadata column

Via the SharePoint 2010 UI I've created a DocumentLibrary and added a new column of type Managed Metadata. I've associated a TermSet with this column and all works fine. Now I need to create a deployment package in Visual Studio 2010. It is easy enough to create the Document Library. I've followed the below steps 1. Create empty SharePoint 2010 project 2. Added new Empty Element to the project. In the Elements.xml I have <Field ID="{15712E76-A603-43AB-885E-CE48BD216381}" Type="TaxonomyFieldTypeMulti" Name="DocTags" DisplayName="DocTags" ShowInDisplayForm="TRUE" ShowInEditForm="TRUE" ShowField="Term1033" Required="FALSE" StaticName="DocTagsInternal" Group="NotSure"/> 3. Added ContentType to the project. Added FieldRef elements to the Element.xml 4. Added List Definition From Content Type and associated it with the above Content Type. 5. Created a List Instance of the above List Definition. 6. Build and Deploy. 7. Associate the column with a Term Set. 8. Add Document to the Document Library. I can select a Term from the applicable TermSet, but when I save the document I get: Failed to get value of the "DocTags" column from the "Managed Metadata" field type control.  See details in log. Exception message: Invalid field name. {00000000-0000-0000-000

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

wrapping a table into a multi column page

I have a table populated with some data fields that spans 5 pages.  I am trying to set up the layout to be a multi column report (just two columns) in which the table wraps at the end to populate the right section of the page How can this be done?Javier Guillen
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