Azure Data Factory: How to use Stored Procedures with output Parameters

Introduction

In version-1 of Azure Data Factory, we don’t have greater flexibility to use stored procedures as a default activity. However, Microsoft came with adding this feature to call the Database-Stored Procedures in the version-2 which is under public preview mode currently. But, the stored procedure activity in version-2 supports passing the normal parameters to the procedures –but we cannot use the output parameters in the stored procedure activity in the version-2 yet. Thought of having this blog as I had a specific requirement for which  I didn’t find any alternatives from the documentations/stack overflow/tech net as easily. So, I felt having this blog in detail will be useful for the folks who are all looking for a solution in case they have similar requirements. As there are business cases which needs the output of the procedure used to further reference the sub-subsequent activities use in activities. (example- Logging of all the activities in the database with a hierarchy of Batch-Task-Sub Task) where Batch ID has to be used in all sub task-activities and Batch ID is generated using a stored procedure activity.)

Work-Around:

As a workaround to implement this functionality, we can use Lookup Activity as an alternative. In that, we can execute the stored procedures – along with the flexibility to capture the output as below. Note: This solution/workaround is helpful if the output to be captured from the stored procedure is a single row and not multiple rows.

Steps to make it functional

Step-1 Creates required linked service for SQL Database:

{
    "name": "SQLDatabasetest1",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "integrated security=False;encrypt=True;connection timeout=30;data source=<Servername>;initial catalog=<Databasename>;user id=<userid>",
            "encryptedCredential": "encrypted_credential"
        }
    }
}

Step-2 Creates Test Stored Procedure in SQL DB:

**Note: **With this approach, it’s optional to use output parameter by simply selecting the result within sproc or we can use an output parameter.

With Output parameter

CREATE  PROCEDURE sp_testproc (@TEST INT OUTPUT)
AS
BEGIN
DECLARE  @TESTINT INT  = 100
SET  @TEST = @TESTINT
END
GO

Without the Output parameter

CREATE PROCEDURE  sp_testproc
AS
BEGIN
DECLARE @TESTINT INT = 100
SELECT @TESTINT AS TEST
END
GO

Step-3 Creates Dataset, Lookup Activity with stored Procedure:

As Dataset should be mandatory for Lookup Activity however we don’t need any schema/metadata - Let's Create Dataset.

Sample Dataset creation

{
    "name": "AzureDBDataset",
    "properties": {
        "linkedServiceName": {
            "referenceName": "SQLDatabasetest1",
            "type": "LinkedServiceReference"
        },
        "type": "AzureSqlTable"
    }
}

Lookup Activity with output parameter:

{
    "name": "Test_Lookup",
    "type": "Lookup",
    "policy": {
        "timeout": "7.00:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false
    },
    "typeProperties": {
        "source": {
            "type": "SqlSource",
            "sqlReaderStoredProcedureName": "[dbo].[sp_testproc]",
            "storedProcedureParameters": {
                "TEST": {
                    "type": "Int32",
                    "value": "1"
                }
            }
        },
        "dataset": {
            "referenceName": "AzureDBDataset",
            "type": "DatasetReference"
        }
    }
}

Lookup Activity without output parameter

{
    "name": "Test_Lookup",
    "type": "Lookup",
    "policy": {
        "timeout": "7.00:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false
    },
    "typeProperties": {
        "source": {
            "type": "SqlSource",
            "sqlReaderStoredProcedureName": "[dbo].[sp_testproc]"
        },
        "dataset": {
            "referenceName": "AzureDBDataset",
            "type": "DatasetReference"
        }
    }
}

Now let’s use the below code in the Dynamic content to access the output value from the created stored procedure

Dynamic Expression -

@activity('Test_Lookup').output.firstRow.TEST