Skip to content

Latest commit

 

History

History
92 lines (72 loc) · 6.07 KB

transform-data-using-stored-procedure.md

File metadata and controls

92 lines (72 loc) · 6.07 KB
title description services documentationcenter author manager ms.service ms.workload ms.tgt_pltfrm ms.devlang ms.topic ms.date ms.author
Transform data by using the Stored Procedure activity in Azure Data Factory | Microsoft Docs
Explains how to use SQL Server Stored Procedure Activity to invoke a stored procedure in an Azure SQL Database/Data Warehouse from a Data Factory pipeline.
data-factory
douglaslMS
craigg
data-factory
data-services
na
na
conceptual
11/27/2018
douglasl

Transform data by using the SQL Server Stored Procedure activity in Azure Data Factory

[!div class="op_single_selector" title1="Select the version of Data Factory service you are using:"]

You use data transformation activities in a Data Factory pipeline to transform and process raw data into predictions and insights. The Stored Procedure Activity is one of the transformation activities that Data Factory supports. This article builds on the transform data article, which presents a general overview of data transformation and the supported transformation activities in Data Factory.

Note

If you are new to Azure Data Factory, read through Introduction to Azure Data Factory and do the tutorial: Tutorial: transform data before reading this article.

You can use the Stored Procedure Activity to invoke a stored procedure in one of the following data stores in your enterprise or on an Azure virtual machine (VM):

  • Azure SQL Database
  • Azure SQL Data Warehouse
  • SQL Server Database. If you are using SQL Server, install Self-hosted integration runtime on the same machine that hosts the database or on a separate machine that has access to the database. Self-Hosted integration runtime is a component that connects data sources on-premises/on Azure VM with cloud services in a secure and managed way. See Self-hosted integration runtime article for details.

Important

When copying data into Azure SQL Database or SQL Server, you can configure the SqlSink in copy activity to invoke a stored procedure by using the sqlWriterStoredProcedureName property. For details about the property, see following connector articles: Azure SQL Database, SQL Server. Invoking a stored procedure while copying data into an Azure SQL Data Warehouse by using a copy activity is not supported. But, you can use the stored procedure activity to invoke a stored procedure in a SQL Data Warehouse.

When copying data from Azure SQL Database or SQL Server or Azure SQL Data Warehouse, you can configure SqlSource in copy activity to invoke a stored procedure to read data from the source database by using the sqlReaderStoredProcedureName property. For more information, see the following connector articles: Azure SQL Database, SQL Server, Azure SQL Data Warehouse

Syntax details

Here is the JSON format for defining a Stored Procedure Activity:

{
    "name": "Stored Procedure Activity",
    "description":"Description",
    "type": "SqlServerStoredProcedure",
    "linkedServiceName": {
        "referenceName": "AzureSqlLinkedService",
        "type": "LinkedServiceReference"
    },
    "typeProperties": {
        "storedProcedureName": "sp_sample",
        "storedProcedureParameters": {
            "identifier": { "value": "1", "type": "Int" },
            "stringData": { "value": "str1" }

        }
    }
}

The following table describes these JSON properties:

Property Description Required
name Name of the activity Yes
description Text describing what the activity is used for No
type For Stored Procedure Activity, the activity type is SqlServerStoredProcedure Yes
linkedServiceName Reference to the Azure SQL Database or Azure SQL Data Warehouse or SQL Server registered as a linked service in Data Factory. To learn about this linked service, see Compute linked services article. Yes
storedProcedureName Specify the name of the stored procedure to invoke. Yes
storedProcedureParameters Specify the values for stored procedure parameters. Use "param1": { "value": "param1Value","type":"param1Type" } to pass parameter values and their type supported by the data source. If you need to pass null for a parameter, use "param1": { "value": null } (all lower case). No

Error info

When a stored procedure fails and returns error details, you can't capture the error info directly in the activity output. However, Data Factory pumps all of its activity run events to Azure Monitor. Among the events that Data Factory pumps to Azure Monitor, it pushes error details there. You can, for example, set up email alerts from those events. For more info, see Alert and Monitor data factories using Azure Monitor.

Next steps

See the following articles that explain how to transform data in other ways: