How to get output parameter of a stored procedure in SSIS package using C#?

See more:

C#

SSIS

stored-procedure

I want to get output parameter of a Stored procedure in SSIS Package using C#. I am able to save data if it is a new record. But I am not getting the output parameter value while executing the package.

Core Code:

Application app = new Application();

              //Package package = app.LoadPackage(@"E:\SSIS\SSIS_SP_Call_3\SSIS_SP_Call_3\Package1.dtsx", null);//Package Full Path(Configurable)

              Package package = app.LoadPackage(@"E:\SSIS\SSIS_SP_Call_3\SSIS_SP_Call_3\Package2.dtsx", null);//Package Full Path(Configurable)


              Variables vars = package.Variables;


              vars["Country"].Value = "India"; //Value of the Variable set in the Package
              vars["State"].Value = "Kerala1";//Value of the Variable set in the Package


              string Result = vars["Result"].Value.ToString();//Value of the Variable set in the Package




              Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();//Excecute the SSIS Package (local_DtsError.Description.ToString())

What I have tried:

SP as below:

USE [testssis]
GO
/****** Object:  StoredProcedure [dbo].[InsertData]    Script Date: 04/11/2018 11:51:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC InsertData_Output @Country,@State,@Result 

--EXEC InsertData_Output 'India','Kerala','' 


ALTER PROCEDURE [dbo].[InsertData_Output]
(
      @Country    NVARCHAR(50)
    , @State      NVARCHAR(50)
                   
    ,@Result VARCHAR(1000) OUTPUT           
)
AS
BEGIN

declare @UserCount VARCHAR(40)

    SET NOCOUNT ON;
     
SELECT @UserCount = COUNT(*) FROM SSISUsingCS WHERE State = @State;

--SELECT @UserCount
 
IF(@UserCount > 0)
begin
 
 select  @Result = 'Record already exists';
 --select @Result
 
end
else
begin
    INSERT INTO dbo.SSISUsingCS (Country, State) VALUES(@Country, @State)
    Set  @Result = 'Record Saved Successfully';
    --select @Result
    
end
END

Code is as below:

private void CallSSIS_SP_SAVE_OUTPUT()
       {
           try
           {
               Application app = new Application();

               //Package package = app.LoadPackage(@"E:\SSIS\SSIS_SP_Call_3\SSIS_SP_Call_3\Package1.dtsx", null);//Package Full Path(Configurable)

               Package package = app.LoadPackage(@"E:\SSIS\SSIS_SP_Call_3\SSIS_SP_Call_3\Package2.dtsx", null);//Package Full Path(Configurable)


               Variables vars = package.Variables;


               vars["Country"].Value = "India"; //Value of the Variable set in the Package
               vars["State"].Value = "Kerala1";//Value of the Variable set in the Package


               string Result = vars["Result"].Value.ToString();//Value of the Variable set in the Package




               Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();//Excecute the SSIS Package (local_DtsError.Description.ToString())



               if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
               {

                   foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
                   {



                       Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());

                       Console.WriteLine();

                   }

               }

           }
           catch (Exception ex)
           {

           }

       }