Can you Cache a SSIS Lookup component to Foxpro v7 with parameterized query?

I am using a lookup component in a SSIS data flow. The lookup is a select to a foxpro table. THe lookup works fine with full cache selected. I cannot get the lookup to work with a partial or no cache. I have the latest Foxpro OLE DB driver installed which I understand to support paramaterized queries. Has anyone had success with using cached lookup to Foxpro Does anyone know how to set the lookup properties of sqlcommand and sqlcommandparam I am unable to find any examples in BOL or on the web.

Here are some details. IF I go with "use a table or a view" option with the default cache query I get initialization errors

[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E14 Description: "Command contains unrecognized phrase/keyword.".

In the advanced editor I see

SQLCommand set to

"select * from `kcf`"

and SQLCommandParam set to

"select * from
(select * from `kcf`) as refTable
where [refTable].[patkey] = and [refTable].[dayof_stay] = and [refTable].[modifier] = and [refTable].[kcf_code] = and [refTable].[source] = and [refTable].[kcf_time] = "

I believe the above error is because Foxpro V7 does not support the inner subselect . In addition the query contains CRLF without a continuation character (";").

If I remove the CRLF in the sqlcommandparam query, using the advanced editor, I get this design time error "OLE D error occurred while loading column metadata. Check the sqlcommand and sqlcommandparam properties". The designer requires both properties to be set, its unclear to me how the interact.

I cannot find any examples in BOL or on the web on how to set these 2 properties. Can someone give me a few guidelines

I can get past the design errors by changing sqlcommandparam to a plain select that is VFP 7 compatible ( I removed the subselect and the square brackets):

select * from kcf as refTable where refTable.patkey = and refTable.dayof_stay = and refTable.modifier = and refTable.kcf_code = and refTable.source = and refTable.kcf_time =

But then I get a runtime error

[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E46. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E46 Description: "One or more accessor flags were invalid.".

[lkp_lab_worst_value [6170]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.

Any idea on what I should try next