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

http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspxhttp://www.rad.pasfu.com

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

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

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

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

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

what is error message exactly?http://www.rad.pasfu.com

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

Hi , Please help me.THis scenario become a critical requirment for me now.Thanks, A2H

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

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

could you paste your stored procedure script here? http://www.rad.pasfu.com

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

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

I am little bit new to stored procedures. Can u please guide me correctly to create a valid sp which will work fineThanks, A2H

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

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

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