.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

Lookup Transform does not match on CASE

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
Hello all: I have a simple lookup table with  Code (PK, INT) and a Description (VARCHAR(100)) fields. There is a Unique Index on [Description]. There is an entry with [Description] = 'Other'. If I try to INSERT a value of 'OTHER' it will fail because of the violation of the UNIQUE Constraint. If I SELECT from this table WHERE [Description] = 'OTHER' I will get the one row with 'Other'. All is well and good, this is as it should be. I then reference that table in a Lookup Transform in SSIS, to 'lookup' the [Code] for any given [Description] coming down the data flow pipeline. When it encounters a value of 'OTHER' it does NOT make a match to the row with 'Other'. Seems the collation rules of SSIS are NOT respecting that of the SQL Server. Yes, I could always do a UPPER on both sides so as to switch everything to upper case, but I shouldn't HAVE TO. Is there anything else I could do to make SSIS think the 'Other' and 'OTHER' are equal, and therefore should be matched? Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

View Complete Post

More Related Resource Links

Lookup Transformation: Fails to match in full cache mode both fields same case and collation


Hi All

I am at wits end trying to resolve a lookup transformation issue where lookups fail to match in full cache mode. 


1. BIDS 2008

2. Both tables are in the same db with the same collation (Latin1_General_CI_AI)

3. Both columns have the same collation (Latin1_General_CI_AI)

4. Both values are the same case

5. Both columns are varchar(9)

I'm not sure what else there is to check. I would prefer to understand what is causing the lookups to fail vs using partial cache.

Any suggestions welcome!


Lookup transform partial cache problem.

Hi, I've a simple lookup transform in SSIS 2008 (R2). I've created it with a full cache and it worked fine. When i switch to partial cache, it will give me this error: -------------------------------------------------------------------------------------------------- TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at DFT_AdventureWorks [Lookup [411]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Syntax error, permission violation, or other nonspecific error". Error at DFT_AdventureWorks [Lookup [411]]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Error at DFT_AdventureWorks [SSIS.Pipeline]: "component "Lookup" (411)" failed validation and returned validation status "VS_ISBROKEN". Error at DFT_AdventureWorks [SSIS.Pipeline]: One or more component failed validation. Error at DFT_AdventureWorks: There were errors during task validation.  (Microsoft.DataTransformationServices.VsIntegration) -------------------------------------------------------------------------------------------------- i've

Merge Join: Full Outer Join - keep key values in case of no-match

I'm using the Merge Join to join several different incoming flows into one flow.  I've configured the joins to use a Full Outer Join because I need all records from all sources. In the case of a no-match, I want the component to keep the values of join key fields instead of setting them to NULL.  How can I achieve that?  (Activating the checkbox doesn't help, because that adds a new field to the output instead of re-using the existing one.)

Wierd case of: Msg 213, Column name or number of supplied values does not match table definition

Hi, I'm working on several triggers (that happen after insert or update) in order to log the changes in a different table. They all follow a similar syntax and are working fine, except for this one... I've reduced the next code to the minimum that gives an error, so we can safely assume the other parts of the trigger are working fine. INSERT INTO [Adt].[WardUnitStayLog] SELECT t.* FROM [Adt].[WardUnitStay] t INNER JOIN inserted i ON i.[Id] = t.[Id]; I've used this same syntax (but on different tables) in other triggers, and these are working perfectly fine. The above query provides the next error: Column name or number of supplied values does not match table definition. I've checked both tables for differences in the columns, but to no avail... (I've checked them manually and by outerjoining the information_schema.columns) (I've also checked the order in wich these columns are defined, they match over the two tables) These are the creation scripts for the tables: CREATE TABLE [Adt].[WardUnitStay] ( [Id] [dbo].[Id] IDENTITY(1,1) NOT NULL, [UnifiedUnitStayId] [dbo].[Id] NOT NULL, [WardCd] [dbo].[Cd] NOT NULL, [_FirstAtTm] [dbo].[Dtm] NOT NULL, [_IsReservation] BIT NOT NULL, [_LastAtTm] [dbo].[Dtm] NOT NULL, [_LastBedCd] [dbo].[Cd] NULL, [_LastPhysicianUid]

Case Statement with 'is null' lookup.


I have a case statement that is supposed to return different answers depending on the criteria in the when clause:

Select [Unit Name], StartDate, EndDate, Operation, Production, 'Availability' = 
case when Operation = 'PO' or Operation = 'IR' or Operation = 'MB' or Operation = 'ME' and Production = '0' and <strong><span style="text-decoration:underline">EndDate is null</span></strong> then 'Unavailable'
Else 'Available'

If the case is anything else other tha

SPD 2007 Workflow: Fetch a value from a lookup field? (Case Mgmt for Govt Agencies Template)


Hi, I need a workflow on a list which will fetch a value from a lookup field in another list.

With the "Case Management for Government Agencies" template, the "Case" field in the Tasks list ls a lookup field on the Cases (Case Number) list.  Now, I just can't find a way of mapping the proper Case Number from the Cases lists using a workflow stated in my "Decisions" list.  Moreover, the workflow completes but the Case field in the Tasks list remains empty (although it is a mandatory field: "must have a value") ?!?

Visual Case Tool - UML Tutorial

The Class Diagram

The class diagram is core to object-oriented design. It describes the types of objects in the system and the static relationships between them.

Use Case Diagram

The use case diagram shows the functionality of the system from an outside-in viewpoint.

Actors (stick men) are anything outside the system that interacts with the system.

The number of members in the conceptual type ... does not match with the number of members on the ob



I've added a scalar property to an entity and now ASP.NET Dynamic Data scaffolding throws this error:

The number of members in the conceptual type 'TrainingModel.Users' does not match with the number of members on the object side type 'TrainingModel.Users'. Make sure the number of members are the same.

This happens at the following line in global.asax:
model.RegisterContext(typeof(TrainingModel.TrainingEntities), new ContextConfiguration() { ScaffoldAllTables = true });

Please help!



Hello, i need help changing the following code into a switch...case statementUndecided

<script runat="server">
void Page_Load()
    if (Page.IsPostBack)
    public String toSring()
    int intcomputerChoice=1;
       if (Page.IsPostBack)

Editor's Note: In Case You Hadn't Noticed .


Find out what's new for MSDN Magazine, including a print redesign and the introduction of virtual labs on our web site so you can experiment with our code.

Howard Dierking

MSDN Magazine June 2008

Advanced Basics: A Match-Making Game in Visual Basic


My four-year-old son has decided that he wants to be like his dad when he grows up. He is planning to work in my office, and write computer programs just like I do. But there is one problem-he thinks I write games.

Duncan Mackenzie

MSDN Magazine October 2005

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