Stored procedure from SSIS
-
Hello CP, I'm messing around with SSIS and have run into a problem when trying to get the DataReader Source to work. The problem is simple; I'm trying to set the SqlCommand property to point to a stored procedure: EXECUTE [dbo].[spMyProc] '20110101', '20110131' The problem does not lie in calling the SP but rather with something in the SP. My SP makes use of Temp table and it seems it cannot find it:
Error at Data Flow Taks[DataReader Source[1]]: System.Data.SqlClient.SqlException: Invalid object name '#tmpCalcTable'.
at System.Data.SqlConnection.OnError(SqlException exception, Boolean breakConnection)The Stored Procedure creates a temporary table and later deletes it, but it seems it's either not doing that or can't access the it. I've tried making the temp table global by using a double hash (##) instead of one but without luck. Does anyone have an idea how I can fix this?
-
Hello CP, I'm messing around with SSIS and have run into a problem when trying to get the DataReader Source to work. The problem is simple; I'm trying to set the SqlCommand property to point to a stored procedure: EXECUTE [dbo].[spMyProc] '20110101', '20110131' The problem does not lie in calling the SP but rather with something in the SP. My SP makes use of Temp table and it seems it cannot find it:
Error at Data Flow Taks[DataReader Source[1]]: System.Data.SqlClient.SqlException: Invalid object name '#tmpCalcTable'.
at System.Data.SqlConnection.OnError(SqlException exception, Boolean breakConnection)The Stored Procedure creates a temporary table and later deletes it, but it seems it's either not doing that or can't access the it. I've tried making the temp table global by using a double hash (##) instead of one but without luck. Does anyone have an idea how I can fix this?
Have you tried using a table variable instead of a temp table? This article has some useful information: Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance[^].
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
Hello CP, I'm messing around with SSIS and have run into a problem when trying to get the DataReader Source to work. The problem is simple; I'm trying to set the SqlCommand property to point to a stored procedure: EXECUTE [dbo].[spMyProc] '20110101', '20110131' The problem does not lie in calling the SP but rather with something in the SP. My SP makes use of Temp table and it seems it cannot find it:
Error at Data Flow Taks[DataReader Source[1]]: System.Data.SqlClient.SqlException: Invalid object name '#tmpCalcTable'.
at System.Data.SqlConnection.OnError(SqlException exception, Boolean breakConnection)The Stored Procedure creates a temporary table and later deletes it, but it seems it's either not doing that or can't access the it. I've tried making the temp table global by using a double hash (##) instead of one but without luck. Does anyone have an idea how I can fix this?
The error you're getting back from SQL suggests that there's a temp table being referenced that is either not created, is dropped before it is called, or is otherwise orphaned somehow in the code. Can you run the SP through code (in SSMS) with the appropriate input parameters and it will execute correctly? If so, then it's something in your .NET code or some other setting/parameter within the SSIS package. If not, can you post your TSQL code? Thanks
modified on Friday, June 24, 2011 8:22 AM
-
The error you're getting back from SQL suggests that there's a temp table being referenced that is either not created, is dropped before it is called, or is otherwise orphaned somehow in the code. Can you run the SP through code (in SSMS) with the appropriate input parameters and it will execute correctly? If so, then it's something in your .NET code or some other setting/parameter within the SSIS package. If not, can you post your TSQL code? Thanks
modified on Friday, June 24, 2011 8:22 AM
It works fine in the Server manager. I've got a workaround though, create the temp table once (outside of the query) and truncate it at the start of the query. This also means I'm no longer dropping it. It's a strange little bug though.
The first rule of CListCtrl is you do not talk about CListCtrl - kornman
-
It works fine in the Server manager. I've got a workaround though, create the temp table once (outside of the query) and truncate it at the start of the query. This also means I'm no longer dropping it. It's a strange little bug though.
The first rule of CListCtrl is you do not talk about CListCtrl - kornman
Ok, cool. Glad to assist if you should need further assistance. :cool: