asked
SSIS Error when using Stored Procedure
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 [552]]: 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 [552]]: 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.
You can use temp tables within stored procedures without issue - I've never seen a package fail when a stored procedure used a temp table except for the "regular" reasons around the table being created before it's referenced or being out of scope if it was created in a separate session. You can also use them, with some caveats, directly within ssis but the package will fail validation unless you set it to delay validation.
Yes those are the only controls. It works if I just use a table and not the SP. The temp table is created and dropped inside the sp. I have been thinking about this and I think it might be better if I create a real table earlier in the procedure and then insert the output of the stored procedure into that in a Execute SQL control and then just grab the table and output it. Follow it up by dropping the table I created. Is that the preferred way to go about it?
I just dropped the temp table and everything worked as expected. I guess my only question is can I use temp tables with SSIS in Stored procedures or not?
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.
It wasn't being referenced before it was used as I created it first but I ended up just removing it to get it to work. Thanks for the input though. I will probably go and add the drop if present logic when I go try to retest this. Again thank you.
15 People are following this question.
