SSIS Lookup task with CTE SQL Query and Enable Memory Restriction checked causes Error

Isn't that a mouthful? Common Table Expressions are valid for use against a SQL Server database version 90 and above (2005 & 2008). SSIS works also works for version 90 and above. CTEs are perfectly valid data sources in SSIS. They can be perfectly valid sources in the Lookup component as well, as long as you are using Full Caching. Once you attempt to set the caching to Partial or None, then your package will fail to validate.

The reason for the failure has to do with some of the internal voodoo the Lookup component does to accomplish this. It's readily apparent if you click into the advanced tab and check the box for Custom Query. SSIS takes the most brain dead approach to ensuring it can build out the query in Partial/No cache by wrapping your query with "select * FROM (YOUR QUERY HERE) [RefTable] where [refTable].[critera] = ?" Given the title of this post, the astute reader can see the problem. From BOL: "When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon."

2005 Error message

Validation error. MyDataFlowName: MyLookupTask [84]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

2008 Error message

Error at Data Flow Task [Lookup [52]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Error at Data Flow Task [Lookup [52]]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties.
Error at Data Flow Task [SSIS.Pipeline]: "component "Lookup" (52)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)

So, what to do? Until they fix it, my money is on "Closed, won't fix" as a resolution, the query will have to be rewritten in a non-CTE fashion. Heaven help you if it was a recursive CTE

References:
http://msdn.microsoft.com/en-us/library/ms175972.aspx
https://connect.microsoft.com/SQLServer/feedback/details/531823/ssis-lookup-task-with-cte-sql-query-and-enable-memory-restriction-checked-causes-error