Azure SQL Data Factory Copy Activity with Sink Stored Procedure

0
votes

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.

2
azurestored-proceduresazure-data-factoryazure-data-factory-pipeline
Hey @wwnde, Can you please mention why you want to use stored procedure in sink ? via copy activity you can directly map the source and sink table columns. So understanding your use case would help in identifying the solution – Nandan
@Nandan Once the pipeline is finalized and working, the intention is to modify the stored procedure often to facilitate varied transformations as will be dictated by business owners' needs. This can vary from time to time. I started of with a straight forward direct copy to understand how it works. – wwnde
What errors do you get? – wBob
@wBob Thanks for being willing to help. I sorted it out. Shared my answer below – wwnde

2 Answers

1
votes

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

-1
votes

usually there are 2 ways to data transformations in ADF based on the logic which you see:

for ELT :

  1. You copy activity wherein you copy the data from source into destination as is and then use stored procedures activity to trigger stored procedures that do the needed transformations. SO in your case you can create a table in your sink and via copy activity map the source table to the sink table. Then call the stored procedure activity to do your transformations

enter image description here

for ETL:

  1. There are data flows wherein you can do transformations to your data before loading them into sink