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
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
MCSA | MCP
