CacheType in Lookup for Oracle OLEDB connection

Hi,

1. If I have millions of rows to be compared, then which cache type is prefereed for lookup, Partial or no caching?

2. If I have lookup connected to Oracle Oledb, cache type as partial and SqlCommandParam as following

select * from (SELECT ORDER_ID, OPER_KEY, STEP_KEY, SUM(OCCUR_COUNT) AS OCCUR_COUNT FROM SFWID_OPER_DESC_EXPLD GROUP BY ORDER_ID, OPER_KEY, STEP_KEY) refTable where refTable.ORDER_ID = ? and refTable.OPER_KEY = ? and refTable.STEP_KEY = ?

then it doesn't allow me to add the parameters from Advance tab of Lookup transformation edition, and raises following error

"Provider cannot derive parameter information and SetParametInfo has not been called"

what am I missing?

1. Depends on the amount of memory available and the size of the data set to be cached. A million of rows should not be a big deal; but you do the math; find the size of the row(using just the required columns) and multiply by the number of rows. You should provide a query instead selecting the table from the drop down list.

2. In SSIS mapping parameters in a half-way complex query is some times impossible. So try re-writing the query in a simpler way (perhaps creating a view). BTW, if you parametrize the query; then only partial cache will be used; meaning the query will be executed for every row in the pipeline, affecting negatively the performance of the package

|||

Rafael,

Is it possible that we dont define parameterized query for partial cache?

I had done following things:

For the partial caching, I have checked Enable memory restriction and Enable caching and has set the cache size. I have not checked "Modify sql statement" option. But, "SqlCommandParam" in advanced editor shows the query as follows:

select * from
(SELECT ORDER_ID, OPER_KEY, STEP_KEY, SUM(OCCUR_COUNT) AS OCCUR_COUNT FROM SFWID_OPER_DESC_EXPLD GROUP BY ORDER_ID, OPER_KEY, STEP_KEY) as refTable where [refTable].[ORDER_ID] = ? and [refTable].[OPER_KEY] = ? and [refTable].[STEP_KEY] = ?

But as this is not the perfect syntax for oracle, it showed the error. Thats why I have changed this query to following

select * from (SELECT ORDER_ID, OPER_KEY, STEP_KEY, SUM(OCCUR_COUNT) AS OCCUR_COUNT FROM SFWID_OPER_DESC_EXPLD GROUP BY ORDER_ID, OPER_KEY, STEP_KEY) refTable where refTable.ORDER_ID = ? and refTable.OPER_KEY = ? and refTable.STEP_KEY = ?

Now after the change, running the package throws the following error at this lookup

Error: An OLE DB error has occurred. Error code: 0x80040E5D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E5D Description: "Parameter name is unrecognized.".

So, I went to advanced tab and checked "Modify sql statement" option but it doesnt allow me to add the parameter and throws the error as said in my earlier post.

|||

Is there any special reason for using partial cache? for performance reasons I would not recommned it; but in some cases it is necessary.

*I think* when you use partial cache the lookup uses a query w/parameters; so I don't think you have control over that. I know SSIS parameter mapping and Oracle are not very easy to get working; and I don't know how easy or possible is that in a Lookup transformtion. Search this forum for 'Oracle parameters' to see if you can find somthing helpful.

|||

I searched but couldn't find anything useful. Is it so that Lookup caching doesn't support Oracle parameters ?

Thanks,

Pratibha

|||

Hi Pratibha:

Did you find anything on using parameters in lookups for Oracle OLEDB. Please let me know if you have any workaround. I am also stuck in the same problem.

Any help will be greatly appreciated.

Thanks,

Vipul