Capture and Work with Output Parameters from SQL Stored Procedures using KingswaySoft

In SQL-based languages for RDBMS, Stored Procedures are pivotal when it comes to streamlining your process. They are routines that contain one or more SQL statements that can execute a set of database operation tasks to achieve certain goals. An important aspect of SQL Stored Procedures is, that they have the option to support both input and output parameters, which makes them extremely powerful and flexible. When Output (OUTPUT) Parameters are used, it can return values to the caller which might contain the execution results that might be useful for subsequent tasks or queries. Such capabilities can be very useful in an ETL process. For instance, you may call a Stored Procedure that does a number of things, and at the end of the execution, it returns some results that might be needed in subsequent SSIS data flow or control flow tasks. In order to achieve so, the ETL components or tasks have to be able to work with such input and output parameters in order to fully leverage the benefits. Before we move further, we would like to quickly highlight some of the general benefits when using Stored Procedures.

• Optimized performance, especially in cases with complex SQL queries.
• Code reusability, due to the modular nature of coding, which can be reused multiple times.
• Security, by providing permissions to call procedures, and not to the underlying tables.
• Code readability, once again, due to its modular and abstract nature.

With our most recent 2025 release wave 1, we have specifically introduced the newly added Output Parameter support in various db-related components so that you can efficiently work with those Stored Procedures when Output Parameters are involved.

• Premium ADO NET Command Task
• Premium ADO NET Destination Component
• Premium SQL Command Task
• Premium SQL Server Destination Component
• Amazon Redshift Command Task
• Amazon Redshift Destination Component
• PostgreSQL Command Task
• PostgreSQL Destination Component
• Google AlloyDB Command Task
• Google AlloyDB Destination Component

Once you have updated your SSIS Productivity Pack installation to the latest v25.1 release, you can start to enjoy the newly added flexibility by calling your SQL stored procedures while having access to those output parameters after execution. For further details of this release, you are welcome to check out our change log page.

In this blog post, however, we shall look specifically at our Premium SQL Server components while working with SQL Stored Procedures, and how they can manage it. We will show you how it works with the same Stored Procedure being run by both our SQL Server Command Task, as well as by our SQL Server Destination component, and see how the Output Parameters can be handled in both Control Flow as well as in a Data Flow. For demonstration, we use the following components: 

• Premium SQL Server Command Task
• Premium SQL Server Destination
• Data Spawner (Used as a dummy source for reading variables)
• Premium Derived Column component

Now let's dig in.

Option 1: Using the Premium SQL Server Command Task

The Premium SQL Server Command Task is a control flow task that can be used to execute an SQL command to perform desired db operations. In our example, let's assume that you are getting input values assigned to variables in a previous task or flow, and you would simply wish to pass it to a query or a Stored Procedure and get the output based on that. In this case, rather than have a design-heavy Data Flow, you could use the SQL Server Command Task as a standalone task to capture and use these variables. Let's take a Stored Procedure as an example, where a query is performed, based on the input parameters @FirstName and @LastName, and there are four Output Parameters being defined in it, namely, @Id, @EmailAddress, @Dept, and @Location.

CREATE PROCEDURE GetEmployeeInfo
(
    @FirstName NVARCHAR(10),
    @LastName NVARCHAR(10),
    @Id INT OUTPUT,
    @EmailAddress NVARCHAR(20) OUTPUT,
    @Dept NVARCHAR(10) OUTPUT,
    @Location NVARCHAR(10) OUTPUT
)
AS
BEGIN
    SELECT @Id = id,
           @EmailAddress = email,
           @Dept = DEPT,
           @Location = WORK_LOC
    FROM employee
    WHERE firstname = @FirstName
          and lastname = @LastName;
END;

Now, in our SQL Server Command Task, let's call and execute this Procedure. While doing so, let's map/assign the Input Parameters as the variables. The grid on the left side can be used to drag and drop the desired variables into the command window to parameterize it.

Once done, now it's time to define how those Output Parameters are received after execution. Navigate to the Output page in the Task, and in the grid "Parameter Output", add the defined parameter name from the Stored Procedure, and choose the respective output variable that should hold the value. Click on the green "+" sign to add new lines and add all the Output Parameters.

Now, you can reference the output variables in any succeeding design. In our examples, we are simply using a data flow design with a Premium Derived Column that captures these variables and assigns them to a column for us to read at runtime, using a data viewer.

Please note that the above is just an example to showcase that the Stored Procedure has been executed and the Output Parameters are handled, and you could have these variables used in any design of your choice that suits your requirement. Next, let's look at how the same can be implemented within a Data Flow design.

Option 2: Using the Premium SQL Server Destination component 

Here, say that our requirement is to pass a couple of fields from the upstream Source/Transformation component as the Input Parameters for our Stored Procedure. For this, you can use the Premium SQL Server Destination component and specify the Stored Procedure as a custom command option.

The grid on the left side can be used to select, drag, and drop the required input columns to the command window. Once done, navigate to the Output page, and here, you can add the column name followed by the respective Output parameter name that needs to be assigned to that column. The green "+" sign can be used to add more fields.

When the data flow is executed, you will see that the Stored Procedure is executed successfully, and the Output Parameters are assigned and returned as columns in the default output of the SQL Server Destination component.

The columns can now be used in your data flow design as required.

Conclusion

As demonstrated above, using our Premium SQL Server components, you can easily execute and handle the Output Parameters from Stored Procedures. This gives you the flexibility to perform complex logical designs with ease in either the Control Flow or the Data Flow, by simply using our components' intuitive UI.

We hope this has helped!