Azure IR本地SQL直连Azure SQL存储过程配置错误咨询

Let me break down what went wrong here and walk you through the correct setup:

Your original stored procedure had a critical flaw: it tried to query the on-premises Employee table directly from Azure SQL, which isn't possible—Azure SQL has no access to your local database. Instead, Azure Data Factory (ADF) pulls the data from your local SQL Server first, then passes it to your Azure SQL stored procedure using a table-valued parameter (TVP). That's why you got the error about missing a parameter: ADF expects your stored procedure to accept this TVP as input.

Here's how to fix this step-by-step:

1. Create a Table-Valued Type in Azure SQL #

First, you need a table type that matches the structure of your source Employee table. This type will be used to pass batch data from ADF to your stored procedure.

Run this in your Azure SQL database:

CREATE TYPE [dbo].[EmployeeTableType] AS TABLE(
    [ID] INT,
    [FirstName] NVARCHAR(50), -- Match your source column data types exactly
    [LastName] NVARCHAR(50)
)

2. Write the Correct Stored Procedure #

Now create a stored procedure that accepts this table type as a readonly parameter, then inserts the data into your TestEmployee table:

CREATE PROCEDURE [dbo].[spCopyFromOnPremToAzure]
    @SourceData [dbo].[EmployeeTableType] READONLY -- This is the parameter ADF will use
AS
BEGIN
    SET NOCOUNT ON; -- Prevent extra result sets from interfering with ADF

    -- Insert data from the TVP into your target table
    INSERT INTO [dbo].[TestEmployee] (ID, FirstName, LastName)
    SELECT ID, FirstName, LastName FROM @SourceData;
END

3. Update Your ADF Sink Configuration #

Adjust your sink settings to reference the new table type and parameter. Here's the corrected sink section:

"sink": { 
    "type": "SqlSink", 
    "writeBatchSize": 10000, 
    "sqlWriterStoredProcedureName": "[dbo].[spCopyFromOnPremToAzure]", 
    "sqlWriterTableType": "[dbo].[EmployeeTableType]", -- Use your custom table type, not "TABLE"
    "storedProcedureParameters": {
        "SourceData": { "value": "@{activity('YourCopyActivityName').output}", "type": "Table" }
    }
}

Key Notes to Avoid Future Issues: #

• Match Structures Exactly: The table type, source table, target table, and ADF column mappings must all have identical column names and data types.
• Readonly Parameter: Table-valued parameters in stored procedures must be marked READONLY—this is a SQL Server requirement.
• Forget Direct Source Queries: Never try to access your on-prem database from Azure SQL directly in this workflow; ADF acts as the middleman to transfer the data.

Once you apply these changes, your copy pipeline should run successfully, as ADF will now correctly pass the local SQL data to the stored procedure via the table-valued parameter.

内容的提问来源于stack exchange，提问作者Ivan Nel