Stored Procedures with output Parameters in Azure Data Factory

How to use output Parameters of stored procedures in Azure Data Factory

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-sequent 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 subtask-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 flexibility to capture the output as below.

Note: This solution/workaround is helpful if the output to be captured from storedproc is a single row and not a 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 output parameter.

With Output parameter

CREATE PROCEDURE sp_testproc (@TEST INT OUTPUT)

AS

BEGIN

DECLARE @TESTINT INT = 100

SET @TEST = @TESTINT

END

GO

Without 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

Thanks

Naveen Kumar
MCSA | MCP