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


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".


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


       INSERT INTO [dbo].[PkgLog]
                 SET @PkgExecKey=SCOPE_IDENTITY()

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 

UPDATE dbo.PkgLog
 WHERE PkgExeckey=@PkgExecKey

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 :)


  1. Replies
    1. IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Projects for CSE It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

      JavaScript Online Training in India

      JavaScript Training in India

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

  2. Shweta gaur is one of the famous makeup artist in all over India. We are providing the best makeup artist courses and more other courses in over branches in Delhi.

    Makeup Artist in Delhi
    Makeup Artist
    Best Makeup Artist in Delhi
    Best Makeup Artist in East Delhi
    Top Makeup Artist in Delhi
    Top Makeup Artist in India
    Bridal Makeup

  3. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
    Msbi online training Hyderabad
    Msbi online training India
    Msbi online course
    Msbi course
    Msbi training
    Msbi certification training