[Webinar] Streamline your web hosting managementRegister Today


SSIS: a lookup on an non-OLEDB table

Posted on 2010-03-26
Medium Priority
Last Modified: 2013-11-10
I  have to check for deleted records in the Vendor table.
Therefore my SQL table has the records that may be deleted, and my Vendor using ODBC is missing the records. Therefore the driving table is my OleDB, my lookup needs to be the ODBC. But when I do the lookup it only offers the connection Mgr for the OleDB and it titled OleDB connection Mgr..

I originally was doing this piece successfully in a SQL Stored Procedure using a linked server. The SSIS package will execute it. I wanted to change it to challege myself to learn the SSIS skills.. I fear I tend to lean on my strong skills which is hindering me in developing my newer skills.  But in this case, Is the SQL Stored Procedure really the only way I can do this?
Question by:moosetracker
  • 2
LVL 22

Accepted Solution

PedroCGD earned 1500 total points
ID: 28702123
How many records you have for ODBC lookup?
If you use SQL 2008 you can use the cache component to save those rows in memory or in a file.

Author Comment

ID: 28708428
Nope.. I use SQL 2005, and we have over a million records to compare, 1,048,346 to be exact and growing. Would this be too big if I did have the cache component?

So does that mean the Stored procedure?

Author Closing Comment

ID: 31707629
Would have liked to have known more about the size of the cache in SQL 2008.. Like right now I am storing these 1 million plus records in a table variable in the Stored procedure.. So if I can do that, will the cache component in the SSIS hold the same amount of records..?

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question