Getting Output value from a Stored Procedure in Execute SQL
Hi, I have an Execute SQL task ,in which I am having an stored procedure. My requirement is I have to map the output value of sp to the output value of my Execute SQl Task. So that once after the Execution of ExecuteSQL Task.I can have a check in precedence constraints and based on that I can move forward.Thanks, A2H
October 27th, 2010 11:18am

http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspxhttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
October 27th, 2010 11:33am

Hi Reza, How can i stop the execution of ssis package if any error occurs in Stored Procedure. My Task will be ExecuteSql task only.Thanks, A2H
October 28th, 2010 12:35am

Hi Reza, How can i stop the execution of ssis package if any error occurs in Stored Procedure. My Task will be ExecuteSql task only. Thanks, A2H right click on the task, go to properties window, and set FailPackageOnFailure to Truehttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 1:52am

Hi, One more doubt I am able to map a single output parameter. How can we map mutiple output parameters.I have tried like this EXEC SPName ?Output,?Output and in the parameter mapping i have set the second parameter name as 1. But its giving me an error. Please help me.Thanks, A2H
October 28th, 2010 8:05am

what is your connection type? is it OLEDB? what is your error? do you have two output parameters in your SPName?http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 8:10am

Yes my sp has two output parameters. My Connection type is oledb Difference I can see here is one output parameter is int and second output parameter is string. Thanks, A2H
October 28th, 2010 8:13am

what is error message exactly?http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 8:18am

Hi, Below is the Error Message which I got [Execute SQL Task] Error: Executing the query "EXEC [dbo].[SPName] ? output,?output" failed with the following error: "Must declare the scalar variable "@P2output".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Thanks, A2H
October 28th, 2010 9:45am

Hi , Please help me.THis scenario become a critical requirment for me now.Thanks, A2H
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 11:22pm

I can't understand where the @P2output variable comes from? did you set it in parameter mappings tab? what is parameter name there?http://www.rad.pasfu.com
October 29th, 2010 2:57am

I am trying to get two output parameters from Stored procedure. I have set the second out put parameter in parameter mapping tab.Thanks, A2H
Free Windows Admin Tool Kit Click here and download it now
October 29th, 2010 4:09am

could you paste your stored procedure script here? http://www.rad.pasfu.com
October 29th, 2010 1:34pm

Hi Reza, Sorry I was on a vacation ,so couldnt reply on time. Below is the script CreatePROCEDURE [dbo].[Sample SP] @Result BIT,@ErrMsg VARCHAR(500) OUTPUT AS BEGIN SET NOCOUNT ON ; DECLARE @Result BIT BEGIN TRY BEGIN TRANSACTION -- Business Logic Reside Here SET @Result = 1 -- Success SET @ErrMsg = '' SELECT @Result 'Status',@ErrMsg 'ErrorMessage' COMMIT TRANSACTION END TRY --Catch Block BEGIN CATCH SET @ReturnStatus = 0 -- Fail SET @ErrMsg = ERROR_MESSAGE() SELECT @Result 'Status',@ErrMsg 'ErrorMessage' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION END CATCH END Here I have two ouput parameters.I have to map these two ouput parameters to Execute Sql Task in SSIS Package.Thanks, A2H
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 5:23am

Is your stored procedure correct syntactically?!!! you declare @Result parameter twise in the stored procedure!!!! also there is not Output Definition for @Result parameter, and also I see a SELECT statement at the last line before the IF clause. if you return values in this way ( with a select statement) , then you don't need any output parameters, you can remove parameters. Totally, I think you need more changes in the stored procedure, are you new in stored procedures?http://www.rad.pasfu.com
November 5th, 2010 9:30am

I am little bit new to stored procedures. Can u please guide me correctly to create a valid sp which will work fineThanks, A2H
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 9:39am

try this stored procedure now: alter PROCEDURE [dbo].[SampleSP] AS BEGIN SET NOCOUNT ON ; DECLARE @Result BIT declare @ErrMsg VARCHAR(500) BEGIN TRY BEGIN TRANSACTION -- Business Logic Reside Here SET @Result = 1 -- Success SET @ErrMsg = '' SELECT @Result 'Status' as result,@ErrMsg 'ErrorMessage' as errmsg COMMIT TRANSACTION END TRY --Catch Block BEGIN CATCH SET @ReturnStatus = 0 -- Fail SET @ErrMsg = ERROR_MESSAGE() SELECT @Result 'Status' as result,@ErrMsg 'ErrorMessage' as errmsg IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION END CATCH END and then in execute sql task, set sqlstatement with : exec dbo.SampleSP set ResultSet property to single row and delete every thing you configured in parameter mappings tab then in the Result Set tab, set variables with result names like this: variable name result name -------------------------------------------- User::varResult result User::varErrMessage errmsg http://www.rad.pasfu.com
November 5th, 2010 9:52am

Hi Reza, That did the Trick.With some slight modifications I was able to get the value correctly.:) Thanks for ur valuable help. I got it where I was doing wrong:( One more Help please : Can u tell me some good e books or website which will help me to get well wersed in Stored Procedures.Thanks, A2H
Free Windows Admin Tool Kit Click here and download it now
November 7th, 2010 10:30pm

Glad to help, I recommend msdn links, like this: http://msdn.microsoft.com/en-us/library/ms378046%28v=SQL.90%29.aspxhttp://www.rad.pasfu.com
November 7th, 2010 11:49pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics