.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

SQL CE connections in Lookup transformation

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server

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]

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  

DVWP connections with multi-select lookup columns

I have a question concerning the Data View Web Part using SharePoint Designer 2007.   I have two list (A and B). List A has a lookup column (called B-items) with multiple selections to items in List B(using the Title column from B).   I create a web part page and insert a data view of using List A. I create another data view with data from List B. Next, I make a Web Part Connection between the two data views with A passing B-items column as parameter to List B data view. I then create a filter on the List B data view with a comparison of List B’s Title column equal to the parameter of List A’s B-items value when a user selects an item from List A data view. The problem is nothing appears in List B’s data view.   When I try the reverse of the above scenario it works fine.     I understand that this is properly functionality, but is there a way to achieve my first scenario? If so, how can it be accomplished?   I have looked through the web for help and found the answer here http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/749b7477-f37f-4724-94b3-b6ace770e73a seems to be what I’m looking for. However, I am unsure how to implement his code into the web part page.   I am a worse than a novice with xsl so if anyone gives an example I would greatly appreciate step by step on what code is needed a

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   

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.



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!


Lookup connections

The second connection of LookUp can only be a OLE DB connection, how to workaround that if I was using other typies souce? What's the best option? (If there is) thanks

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

fx Controls instead of Drop Down Lists for Lookup fields in InfoPath Secondary Data Connections


I have a secondary data connection with a lookup field into another list. To display the fields text value instead of the index, I am using a drop down list which gets its choices from the linked list. This information is read-only so I disabled the control via formatting rule.

A calculated value control might be better suited, but my XPath skills are not very "developed". Has anyone done this before? Taken the index from the bound field and extracted the text value from a second data connection? Thx


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

Basic Instincts: Introducing ASP.NET Web Part Connections


When you begin to work with the Microsoft® . NET Framework 2. 0 and ASP. NET, you discover that the new Web Parts infrastructure adds some very powerful functionality to the underlying platform. In the September 2005 issue of MSDN®Magazine, Fritz Onion and I have an article on programming Web Parts titled "ASP.

Ted Pattison

MSDN Magazine February 2006

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