Skip to content

Latest commit

 

History

History
96 lines (80 loc) · 3.84 KB

data-factory-invoke-stored-procedure-from-copy-activity.md

File metadata and controls

96 lines (80 loc) · 3.84 KB
title description services documentationcenter author manager editor ms.service ms.workload ms.tgt_pltfrm ms.devlang ms.topic ms.date ms.author robots
Invoke stored procedure from Azure Data Factory Copy Activity | Microsoft Docs
Learn how to invoke a stored procedure in Azure SQL Database, or SQL Server from an Azure Data Factory copy activity.
data-factory
linda33wj
craigg
data-factory
data-services
na
na
conceptual
01/10/2018
jingwang
noindex

Invoke stored procedure from copy activity in Azure Data Factory

Note

This article applies to version 1 of Data Factory. If you are using the current version of the Data Factory service, see transform data using stored procedure activity in Data Factory.

When copying data into SQL Server or Azure SQL Database, you can configure the SqlSink in copy activity to invoke a stored procedure. You may want to use the stored procedure to perform any additional processing (merging columns, looking up values, insertion into multiple tables, etc.) is required before inserting data in to the destination table. This feature takes advantage of Table-Valued Parameters.

The following sample shows how to invoke a stored procedure in a SQL Server database from a Data Factory pipeline (copy activity):

Output dataset JSON

In the output dataset JSON, set the type to: SqlServerTable. Set it to AzureSqlTable to use with an Azure SQL database. The value for tableName property must match the name of first parameter of the stored procedure.

{
  "name": "SqlOutput",
  "properties": {
    "type": "SqlServerTable",
    "linkedServiceName": "SqlLinkedService",
    "typeProperties": {
      "tableName": "Marketing"
    },
    "availability": {
      "frequency": "Hour",
      "interval": 1
    }
  }
}

SqlSink section in copy activity JSON

Define the SqlSink section in the copy activity JSON as follows. To invoke a stored procedure while inserting data into the sink/destination database, specify values for both SqlWriterStoredProcedureName and SqlWriterTableType properties. For descriptions of these properties, see SqlSink section in the SQL Server connector article.

"sink":
{
    "type": "SqlSink",
    "SqlWriterTableType": "MarketingType",
    "SqlWriterStoredProcedureName": "spOverwriteMarketing", 
    "storedProcedureParameters":
            {
                "stringData": 
                {
                    "value": "str1"     
                }
            }
}

Stored procedure definition

In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. The stored procedure handles input data from the source data store, and inserts data into a table in the destination database. The name of the first parameter of stored procedure must match the tableName defined in the dataset JSON (Marketing).

CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY, @stringData varchar(256)
AS
BEGIN
    DELETE FROM [dbo].[Marketing] where ProfileID = @stringData
    INSERT [dbo].[Marketing](ProfileID, State)
    SELECT * FROM @Marketing
END

Table type definition

In your database, define the table type with the same name as SqlWriterTableType. The schema of the table type must match the schema of the input dataset.

CREATE TYPE [dbo].[MarketingType] AS TABLE(
    [ProfileID] [varchar](256) NOT NULL,
    [State] [varchar](256) NOT NULL
)

Next steps

Review the following connector articles that for complete JSON examples: