SQL 2008 enterprise, Visual Studio 2008 I'm brand new to SSIS and I'm trying to use SSIS to execute a stored procedure and drop the results off as a csv (technically pipe delimited). I set up the control flow with a data flow task and in the data flow I used and OLE DB Source and a Flat File destination. The connection manager is set up and working as expected. When I choose data access mode SQL command and enter in exec mystoredproc_1 I get back the following 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 '#myfirsttemptable'.". 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. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap) It seems like this should be very straight forward but I've been searching and I can't seem to find why this specific error is occurring. I see that it's finding the first temp table but I'm not sure why. One of the articles I read said I could change to table variables and it would work but I'm not sure if that's the long term solution. I'd prefer the stored procedure because I'm doing a bunch of case statement data swaps inside it and don't want to have to rewrite it in SSIS. Mostly because it already works if I simply cut paste into excel and save it. If I have to do the data manipulation in SSIS rather than the SP then I will but I'm hoping that I'm making a very simple mistake. I appreciate any advice that you can give me.
By the looks of the error that you have posted, it seems the issue resides inside the stored procedure itself. Before you go about debugging, I assume that the final data which you want to load onto a flat-file in pipe-delimited format is coming from the temporary table called #myfirsttemptable. Check whether #myfirsttemptable is being referenced anywhere in the query before it has been actually created. It will be nice if you do the drop and create of this table at the beginning of the SP itself. A sample format is shown below- ALTER PROCEDURE mystoredproc_1 AS BEGIN IF OBJECT_ID('tempdb.dbo.#myfirsttemptable') IS NOT NULL DROP TABLE #myfirsttemptable -- WRITE YOUR LOGIC FOR CREATING THE TEMP TABLE AND POPULATING IT WITH DATA SELECT COLUMN_LIST FROM #myfirsttemptable END The final SELECT will then act as the source-feed to your flat-file destination of the Data Flow Task. Hope this helps.