Link to home
Start Free TrialLog in
Avatar of moosetracker
moosetracker

asked on

SSIS: a lookup on an non-OLEDB table

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?
ASKER CERTIFIED SOLUTION
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of moosetracker
moosetracker

ASKER

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