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

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

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

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

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!


View Complete Post

More Related Resource Links

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

Lookup Transform does not match on CASE

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.

SSIS Lookup Transformation Issue

I am having a strange issue, In my data flow i have a Lookup Transformaton which will match on key columns of the fact and is followed by a condetional split that would deside if it should insert (old db destination) or go to update (oledb command) or ignore if no change. I have packages running for the last 1 year with the same logic. But in the recent packages I am experiencing a problem.  example: Key columns for join are - type_no (varchar 16) with all numeric values except one record wihh ' '(space) in it  and type_cd (decimal(18,0)) with values(0,1,2,3,4,5) It worked fine when I test the package. After couple of day running in schedule I get integrity violation and huge file with failed records which are supposed to be blocked at the condetional split as they are already in the fact. When i add a data viewer what i found is for all the llokup columns its having nulls (no match found). Workaround that is working for me for now is - I select full cash and say ok in the lookup transofrmation and again open it and set it back to no cash. Then it starts working as expected. Did anyone come accross this kind of issue? is the some standard that I have to follow to make sure this doesnot happen again  

Move custom list (with lookup fields) to another Web Application

Is it possible to move a custom list with lookup fields to other custom lists from one web application to another? Creating list templates does not work and breaks the relationship. Thanks.   Neil

workflow lookup between 2 fields in 1 list

hi, how can a create a workflow lookup in 1 list between 2 fields. i wan to compare if the 2 fields are in the same row. i create a list with users name and their approvers. the approver must be the unique approver of the user in the same list. can i do this with the workflow in sharepoint designer? thnx

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

How can I use vb codebehind to open an aspx form in "full screen" mode as if I had pushed the F11 ke

I can create an ActiveX control to active F11 to enable full screen mode, but would rather not.  Is there an easier way?   Thanks 

Lookup Task fails in a package when executed as a child but not when executed as a parent

Hi,  I have lose 2 days trying to understanf the following error:  I have a master package with several child ones. When I execute the master one of the child packages suffers an error in a Data Flow Task when executing a Loojup component: [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Retrieve Activity" (894) failed with error code 0x80070057 while processing input "Lookup Input" (895).  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.  The same child package where the error appears contains other lookup tasks that are executed properly.  Surprisingly when the child package is executed alone the error does not appear and the package executes properly.  I have lost 2 days comparing the tasks with other ones and checking properties and I haven't found the clue. I have found in the forum a similar post with a related problem but I don't know how to fix  my problem http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3425381c-267e-4700-afbb-f1faf6a81fdd.     If someones has any idea it will

Full match of two tables

There are 2 tables A and B which have the same structure but different data. is there any high efficient method to find all the different rows in A and B except matching all the columns?Michael

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]

SQL Server 2008 Windows Authentication Mode fails for Database Engine, error 18456

I installed SQL Server 2008 Developer Edition (10.0.1600.22.080709-1414) on a Windows Vista 32-bit development machine with Windows Authentication Mode only (no SQL Server Authentication).   Since this is a development machine, I saw no need for Mixed Mode when I did the install.  The SQL Server Management Studio allows me to login to Analysis Services, Integration Services, Reporting Services, BUT NOT the Database Engine!Windows Authentication for the Database Engine gives the following error: EventID: 18456Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]The 'fix' I have found online is to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine.  Unfortunately, I can't since I didn't install SQL Server Authentication mode (only Windows Authentication Mode).  It appears my only recourse is to uninstall, then reinstall in Mixed mode, then to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine. Before I do so, does anyone know of a better approach?Scott D Duncan

Full Farm Backup Fails on Windows Sharepoint Services Search Database

I am just now starting to us central administration to backup the entire sharepoint farm.  All content backups fine except for the Search Database.  In looking through the backup log, it appears that the search database may not exist.  How can I determine if the search database actually does exist and if it does not can it be recreated?

LookUp Transformation

Hi All I have a flatFile which has 3 columns. Serialnumber,name,string. And i have a DB Table called dbo.devices and i have two columns in it deviceID,Serialnumber. In this table for each serialnumber ,deviceid is a identity generated column. Now i want to replace the SerialNumber Column in the FlatFile with the Correspoding deviceID column in the DB Table. My flat files will have app 20ooo Rows and i have  7000 Files. How can i accomplish this Task. This is just a one time task but i want it to be done in faster way. Am planning to use a Lookup Transformation For this

Script Component Transformation as a Lookup

Hi Guys, I have the following code from my Script Component as a Lookup. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Collections.Generic Imports System.Data.SqlClient <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _ <CLSCompliant(False)> _ Public Class ScriptMain     Inherits UserComponent     Dim connMgr As IDTSConnectionManager100     Dim sqlConn As SqlConnection     Dim sqlCmd As SqlCommand     Private vTaxAmt As Decimal     Private vDelChgAmt As Decimal     Public Overrides Sub AcquireConnections(ByVal Transaction As Object)         connMgr = Me.Connections.Connection         sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)     End Sub     Public Overrides Sub PreExecute()         sqlCmd = New SqlCommand("SELECT ItemTypeCode FROM LkpItemDepartment WHERE (ItemDeptCode = @ItemDeptCode) AND (ItemClassCode = @ItemClassCode)", sqlConn)                 With sqlCmd.Parameters   

supported column types that can be used as additional fields with a Lookup column?

Can anyone tell me which types of columns can be used as additional fields for a Lookup column? Are they the same as the column types that can be used to create Lookup columns? Choice columns are currently not available (known issue) but I think they are officially supported. What about Managed Metadata columns? Thanks

Lookup Transformation Task



Can someone please explain me about the cache definitions in this task? What are the considerations for determining what type of cache to use, whether to use the cache connection manager for full cache, how to determine the best size of a partial cache for each case, etc.



I Can't distiguish between Full and Unresticted in Aggeagation usage case



I Can't distiguish between Full and Unresticted in Aggeagation usage case, is there any concrete example explaining that

Thank you

The complexity resides in the simplicity
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