@Phil

Actual code has #ID. I made mistake while I was copying the code.

This is the error I am getting:

Exception from HRESULT: 0xC0202009

Error at Data Flow Task [OLE DB Source [18]]: 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 11.0"

Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

@KoldCoffee

Yes. I am in process of rewriting report query logic to avoid temp table. But I would still like know/learn how to utilize this kind of store proc because we have lot of report written with temp table...

Thanks!