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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..?
ASKER
So does that mean the Stored procedure?