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

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


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.



View Complete Post

More Related Resource Links

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  

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

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   

Excel File as input paramter for Lookup Data Flow Transformation problems


Before I start, I'm using SQL 2008.

I have a Excel file with email addresses that need to acts at input parameters to a Lookup transformation. I have set the Excel Source to my file and specified the email field to be the output. I have dropped the Lookup Transformation Data Flow and connected the both. I'm going to execute a very simple stored procedure, and under the Connections section my SQL query looks like follows: EXEC Test_GetUserName ? 
When I run that I get an error saying that no parameter was provided. But when I run EXEC Test_GetUserName 'someemail@companyname.com' everything executes great, for the obvious part that the email is hard coded. How do I pass the excel input as the parameter?

Thanks for all the help.

There is 10 types of people in the world, those that understand binary, and those that don't.

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!


SQL CE connections in Lookup transformation


Hi I am trying to use the Lookup transformation to lookup values from a SQL CE database. The problem is that the OleDB data source doesnt have a SQL CE provider. Is there any place to download the SQL CE provider for OLEDB or any alternative way to query data from SQL CE database.

Any help would be highly appreciated. Thanks,

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

SSIS - Lookup Task (Duplicate Row)


Good day,

I'm having difficulty with a lookup task.

Firstly in my Lookup my query contains a "DISTINCT" and my cols match source as only some records fail, Source also has a distinct so no Duplicate there either.

I also added on error to display error records to a dataview. Now the lookup code does exist not sure why he is failing

The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

Is the records locking each other as source & lookup read from same table with diff filter criteria to gether with (NOLOCK) specified.

Please Assist!

Lookup transformation


I am trying to load a Dim table which is type2, I am trying to load past 5years of data at one time. When I am loading it I am sorting the data by statusdate which is going to be SCDStartDate in Target table. When I load the data, I am using two lookups, one for businesss key column, and if that match, I am using lookup for all the rows to see if there is any changed data. If its changed, then it should get inserted and update the already inserted row SCDEndDate = dateadd(ms,-1,statusdate).  When I am using 2nd lookup even when there is an row in target its not updating scdenddate.

Can somebody tell me how to chande the SCDEndDate?. I am using OLEDB command to do that. But I dont know how to get the incoming row statusdate as SCDEnddate.



Script Component Transformation with Lookup


Hi Guys,

I have the following recordset in the data flow.

Col0 Col1 Col2
Itm VM RY FF    128.00

Adding exception table to incremental Load with SSIS Lookup task and conditional split



I have built an incremental load ETL Process to load some flat files with an SSIS Lookup and Conditional Split. I only have one path in the conditional split and that is for New Records.

I have two questions:

1.       For the new records path, I have to check to see if a record exists and I don’t have a single key that is unique, therefore, I have to use a multiple keys to make the record unique.


Below is what I have put in the conditional transformation editor for my first output condition:


Is it a Lookup Transformation or something else?

Hello all,
I'm totaly new to SSIS.  I'm looking for the way to join two tables from different connections and return a result depending on the match.  Tables will be LEFT JOINed by ClientID field.  It is something like a CASE WHEN in a LEFT JOIN.  What I want to accomplish is the same as the following SQL statement:

when table2.ClientID is null then 'MyStatus'
else table2.ClientStatus
end as x
table1 left join table2 on table1.ClientID = table2ClientID

Is this possible in SSIS?  How can this be done?
Thanks in advance

WPF Geometry Transformation Tool

The geometry transformer is a simple tool I wrote to scale-, translate- and rotate-transform a geometry in the path mini language.

calculation, field and map traverse adjustment, and coordinate transformation

Free Pocket PC land surveying software -- COGO calculation, field and map traverse adjustment, and coordinate transformation -- for students and professionals.

Extreme ASP.NET: Text Template Transformation Toolkit and ASP.NET MVC


The Visual Studio T4 code generation engine lets you parse an input file and transform it into an output file. We give you a basic introduction to T4 templates and show you how ASP.NET MVC uses this technology.

Scott Allen

MSDN Magazine January 2010

Parallel Debugging: Debugging Task-Based Parallel Applications in Visual Studio 2010


In Visual C++ 10 and the Microsoft .NET Framework 4, Microsoft is introducing new libraries and runtimes to significantly ease the process of expressing parallelism in your code base, together with new tool support for performance analysis and debugging of parallel applications. In this article, you will learn about debugging support in Visual Studio 2010, much of which is focused on task-based programming models.

Stephen Toub, Daniel Moth

MSDN Magazine September 2009

Windows with C++: Task Scheduler 2.0


The new Task Scheduler in Windows Vista can do so much more than previous versions. We introduce some basic concepts and building blocks you can put to use in your own scheduled task projects.

Kenny Kerr

MSDN Magazine October 2007

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