[Originally posted by]: http://stackoverflow.com/questions/1579476/using-temp-tables-in-ssis
I am using a Temporary table in Stored Procedure in SQL Server. I am trying to use that SP in OLE DB Source Editor.
I can see the data output returned in the Query Builder that comes with Build Query button. But when I Click the Columns tab, I am getting the below error.
– TITLE: Microsoft Visual Studio
Error at Data Flow Task [OLE DB Source ]: 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: “Invalid object name ‘##Payment’.”.
Error at Data Flow Task [OLE DB Source ]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Does that mean I cant use temp tables in SP, if I want it to be consumed by SSIS
For all the hassle involved, I think it is probably just not worth it. Create a real table in the db and truncate it before / after your load. If it’s for a datawarehouse it isn’t going to matter if you have an extra table or two. This gives you the design-time SSIS tools and means you don’t have to worry about the intracacies of temp tables.
If you want to keep things separate, then just create your SSIS temp tables in a separate schema. You can use permissions to make this schmema invisible to all other users.
CREATE SCHEMA [ssis_temp] CREATE TABLE [ssis_temp].[tempTableName]
You can use table variables instead of temporary tables . it will work