SSIS - How To Save Stored Procedure Output Paramter Value To SSIS Package Variable

Scenario:

I need to build custom Logging for our all SSIS Packages. We want to insert some information in a table at start of the SSIS Package and then at the end we want to update the record with "Package Completed successfully" or "Failure".

Solution:

To perform above Task ,we need to have a table first. Here is table definition

CREATE TABLE [dbo].[PkgLog](
   [PkgExecKey] [bigint] IDENTITY(1,1) NOT NULL,
   [PackageName] [varchar](100) NOT NULL,
   [UserName] [varchar](50) NULL,
   [StartDate] [datetime] NOT NULL,
   [EndDate] [datetime] NULL,
   [Status] [varchar](50) NULL)

We will have the Execute SQL Task the very first Task in our SSIS Package that will insert the information in this table. The EndDate and Status will be left blank and they will be populated at the end. We need to write a Stored Procedure that can insert the row in above table and get the PkgExecKey so we can use that at the end of SSIS Package to update the record for EndDate and Status of Execution.

CREATE PROCEDURE [dbo].[InsertPkgLog] 
 @PackageName VARCHAR(100)
,@UserName VARCHAR(100) 
,@StartDate DATETIME,@PkgExecKey INT output

AS

       BEGIN

       INSERT INTO [dbo].[PkgLog]
                  ([PackageName]
                  ,UserName
                  ,[StartDate])
                 
            VALUES
                  (@PackageName
                  ,@UserName
                  ,@StartDate
                 )
                 SET @PkgExecKey=SCOPE_IDENTITY()
                 
       END

If you notice that the @PkgExecKey is output type parameter in our above Stored Procedure. After inserting the record in table, the variable will be holding the identity value. We will save this value to our SSIS variable and then at the end of Package we will use this to update the table column values for EndDate and Status.

Let's create SSIS Variable call PkgExecKey as shown below

Fig 1: Create Variable in SSIS Package

Now bring Execute SQL Task to Control Flow Pane and configure as shown below

Fig 2: Configure Execute SQL Task to get output Parameter from Stored Procedure to SSIS Variable

As shown in fig 2. You need to provide the Connection Manager that is pointing to Database which has the Stored Procedure. You will be writing the statement Exec dbo.InsertPkgLog ?,?,?,?output. Each "?" is to map single parameter. If it is input parameter you will be using "?" but for output parameter you have to write "? output".
Now go to Parameter Mapping and map the parameters to the SSIS Variables.

Fig 3: Map Stored Procedure Parameters ( input and output) to SSIS Variables 

If fig 3, we mapped the Stored Procedure parameters to SSIS Variables. When we map the parameters, we have to provide the Direction either Input or Output. As we can see that our Stored Procedure has 3 input parameters and one output parameter.

Once Execute SQL Task will execute, the Stored Procedure will execute by using input parameters and return value for output parameter(PkgExecKey) and save into SSIS Variable ( PkgExecKey). You can use this variable after different tasks in your SSIS Package. This value can be used to update the dbo.PkgLog Table record.

Let's update the record at the end of package by using PkgExecKey as shown below. I have written a Stored Procedure for this with below definition.

CREATE PROCEDURE [dbo].[UpdatePkgLog] 
 
 @Status VARCHAR(10)
,@PkgExecKey  INT 

AS
BEGIN
UPDATE dbo.PkgLog

SET EndDate=GETDATE()
,Status=@Status
 WHERE PkgExeckey=@PkgExecKey
         END

Fig 4: Updating record by using Stored Procedure in Execute SQL Task

As we can see in fig 4, we are passing two input parameters 'Success' as parameter one and ? that we will map to PkgExecKey variable.

Fig 5: Map input parameter to Stored Procedure In Execute SQL Task.

In this post we learnt how to use Stored Procedure with input and output parameter in Execute SQL task and how to map input and out parameters of Stored Procedure to SSIS Variables. I hope this will help :)