Azure Data Factory Copy Data Activity SQL Sink stored procedure and table-typed parameter in ARM template

2
votes

I am deploying an Azure Data Factory pipeline that contains a Copy Data activity, where the sink is a SQL Server stored procedure. I supply the stored procedure name, which maps to the sqlWriterStoredProcedureName in the ARM Template for the data factory. Recently the interface for setting up the sink in a Copy Data activity changed to include a Table Type Parameter Name, which should map to the TableType (sqlWriterTableType).

enter image description here

What property should I use to set the Table Type Parameter Name? I'm guessing it would be something like sqlWriterTableTypeParameter or sqlWriterTableTypeParameterName.

Can anyone point me in the right direction?

3
azure-data-factoryazure-data-factory-2
I might not be understanding your question, but does it map to storedProcedureTableTypeParameterName? – Jeremy J.
I haven't been able to do a test deployment to see if storedProcedureTableTypeParameterName will work. I'll keep you updated. – Marc Jellinek

3 Answers

0
votes

Have you tried if its the same as the dataset type? SqlServerTable. As seen here: https://docs.microsoft.com/en-us/azure/data-factory/connector-sql-server#dataset-properties

Scrolling down a bit in the documentation appeared this: https://docs.microsoft.com/en-us/azure/data-factory/connector-sql-server#invoke-a-stored-procedure-from-a-sql-sink so I would also try with sqlWriterTableType.

Hope this helped!!

0
votes

I figured this out by leaving the column blank and seeing what ADF gave as the error.

If you have a stored procedure like this:

              CREATE PROCEDURE [dbo].[spFoo]
                    @FooType [dbo].[FooType] READONLY
                AS ...

Then your variables are:

Table Type: "FooType"
Table type parameter name: "FooType" (without the @ symbol)

ADF uses the @ symbol as a reserved word, so just remove that from your input variables and it works as expected.

0
votes

MSFT support finally got back to me. The property to be set in the ARM template that defines the data factory is "storedProcedureTableTypeParameterName"

Using kirikintha's foo example:

CREATE PROCEDURE [dbo].[spFoo]
   @FooTypeVariable [dbo].[FooType] READONLY
AS
...

When calling a SQL stored procedure as a data factory copy activity sink, the ARM template would look like this:

...

    "sink": {
     "type": "AzureSqlSink",
     "sqlWriterStoredProcedureName": "[[dbo].[spFoo]",
     "sqlWriterTableType": "[[dbo].[FooType]",
     "storedProcedureTableTypeParameterName": "FooTypeVariable"
     }

...