CODEWITHSUNDEEP
azurestored-proceduresazure-data-factory
wwnde
wwnde

Reputation: 26676

Azure SQL Data Factory Copy Activity with Sink Stored Procedure

I cant get this going and seem not to find solutions on this forum. I am new to Azure

I have successfully established a direct SQL Query copy activity from on Prem SQL Server to Azure SQL. enter image description here

When I proceed to edit to include a Stored Procedure it fails. I have created a data type and stored procedure as follows in the Azure Sql;

CREATE TYPE [dbo].[Patch] AS TABLE(
    [BaseKey] int,
    [GISKey] [varchar](10),
    [ActiveFrom] datetime
)
DROP PROC IF EXISTS dbo.spCopyPatch
GO
CREATE PROC dbo.spCopyPatch
@Patch dbo.Patch READONLY,
@BaseKey int,
@GISKey varchar(10),
@ActiveFrom datetime
AS
INSERT INTO dbo.Patch( BaseKey,GISKey,ActiveFrom)
VALUES(@BaseKey,@GISKey,@ActiveFrom);
GO

I then proceeded to edit the copy activity Sink. It is able to pick up the Stored Procedure OK;

enter image description here

However this fails on debug: Am I getting it right or missing something?

Your help highly appreciated in advance.

Upvotes: 1

Views: 6736

Answers (1)

wwnde
wwnde

Reputation: 26676

I resolved this and wrote direct from OnPrem SQL Server to Azure SQL Server.

Step 1. Update Source with the a dataset drawn from the SQL.

enter image description here

Step 2. Updated My Stored Procedure to what is highlighted in green below

enter image description here

Step 3. Populate Sink in order and shown below

enter image description here

Debug Copy activity and you have it

enter image description here

Upvotes: 5

Related Questions