How to create stored procedure using Azure Data Factory pipeline?

Question

How do I create stored procedures using Azure Data Factory pipeline?

What activity should I use?

I have an existing pipeline where a stored procedure will be called and and export its data returned to Azure Storage. But the stored procedure I used is manually created.

Now I wanted to include in the pipeline where it will create the stored procedure first rather than creating it manually.

Answer 1

You can use a copy data activity sink to create a Stored Procedure.

In pre-copy script under the sink settings, write your script to create a procedure.

Note: You don’t have an option to write pre-copy script in the source

Below is how I have done this:

1. Create an empty table (dummy) to use as source/sink in copy activity dataset, as we just want to create a stored procedure through this activity.

2. Optional: Create a variable at the pipeline to write your script

sample code: CREATE PROCEDURE p1 AS select 'Hello' as col1

3. Use the variable to execute in the pre-copy script

4. Stored procedure generated in Azure SQL database successful.

5. Now to can call the stored procedure in another copy activity or a new pipeline as per your requirement to copy data to Azure storage.

Copy data activity2:

Source:

Sink:

Output:

Answer 2

If I'm understanding correctly, you're trying to create a stored procedure before you call it in a pipeline Copy Job?

You can use a Pre-copy script to create the Stored Procedure, and if the copy job is targeting that stored procedure, just use the 'Edit' feature and manually enter the name of the procedure.