Data Factory Copy Activity - SYNC - Stored Proc - Has anyone used generic parameter to use same proc on multiple tables?

Blasko, Dan W 20

I am trying to use the copy activity and in the sync side, use the stored procedure option.

It requires two parameters. One of them is a custom data type for the table.

Has anyone figured out a way to use a generic parameter so that this same copy could be used on multiple tables?

I would like one pipeline and not one for each table.

I can build my merge statement inside the stored proc and then call that statement within that proc. I just don't know how to pass a generic table type.

I'm guessing this isn't possible, but hoping someone has a work around.

Trying to avoid loading a staging table because some of the tables are very large data sets and loading everything twice is not an option.

My other thought was using the upsert. But what if someone deletes a source row? How do we remove that from the destination table? Seems like a merge statement would be perfect.

I can get it to work using a stored proc with the type hardcoded in it. Just hoping to make it generic so it can be reused.

phemanth 12,740 Reputation points Microsoft Vendor

2025-01-03T19:02:17.88+00:00
@Blasko, Dan W

Welcome to the Microsoft Q&A and thank you for posting your questions here

It seems like you're trying to create a more efficient and reusable pipeline in Azure Data Factory by using a generic parameter for your stored procedure. This is a common challenge when dealing with large datasets and multiple tables. Here are a few steps

Dynamic SQL in Stored Procedure: You can use dynamic SQL within your stored procedure to handle different table names and structures. This way, you can pass the table name as a parameter and construct your SQL statements dynamically.

Table-Valued Parameters (TVPs): If your stored procedure requires a custom data type, consider using Table-Valued Parameters. You can define a TVP that can be used to pass table data to your stored procedure. This approach allows you to handle different table structures more flexibly.

Metadata-Driven Approach: Create a metadata table that stores information about your source and destination tables. Your pipeline can read from this metadata table and dynamically configure the copy activity and stored procedure parameters based on the metadata.

Using Data Flows: If possible, consider using Mapping Data Flows in Azure Data Factory. Data Flows provide a more visual and flexible way to handle complex transformations and can be configured to handle multiple tables dynamically.

Handling Deletes: For handling deletes, you can implement a soft delete mechanism where you mark rows as deleted in your source table and then propagate these changes to your destination table. Alternatively, you can use a combination of upsert and delete operations within your merge statement to ensure that deletions are handled correctly.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you
Blasko, Dan W 20 Reputation points

2025-01-03T20:25:30.2033333+00:00

I think I explored most of the options you listed. For some reason i guess data flow doesn't support salesforce as a source, because none of my salesforce datasets show up when I go to select a source.

I am loading 400+ tables and I am trying to avoid having a pipeline for each.
Blasko, Dan W 20 Reputation points

2025-01-03T20:28:20.43+00:00

The table type I can pass a dynamic variable, but my sql server stored proc, is the issue. Trying not to have 400 stored procs that all do a merge. I can generate the merge, but the parameter definition is my issue.......

create proc [dbo].[merge_campaignmember_sp] (@campaignmembers dbo.campaignmember_type READONLY)

as
Blasko, Dan W 20 Reputation points

2025-01-03T20:31:16.27+00:00

create proc [dbo].[merge_campaignmember_sp] (@campaignmembers dbo.campaignmember_type READONLY)

as

The above statement is my issue. I don't want 400 seperate stored procs. I want one that I somehow pass the user defined type to. But it needs declared or data factory balks at it.
phemanth 12,740 Reputation points Microsoft Vendor

2025-01-06T06:35:01.62+00:00
@Blasko, Dan W

Thanks for your information

Salesforce is not directly supported as a source in Azure Data Factory's Mapping Data Flows However, you can use the Copy Activity to first copy data from Salesforce to a staging area (like Azure Blob Storage or Azure SQL Database) and then use that as a source in your Data Flows.

Dynamic Table-Valued Parameters: Unfortunately, SQL Server does not support passing table names as parameters directly. However, you can use dynamic SQL within your stored procedure to construct the SQL statement dynamically.

Metadata-Driven Approach: You can create a metadata table that stores the mapping between your source tables and their corresponding table types. Your pipeline can read from this metadata table and dynamically configure the stored procedure parameters based on the metadata.

Handling Deletes: For handling deletes, you can include a step in your merge statement to handle rows that are not matched by the source. This way, you can ensure that deletions are propagated to the destination table.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you

Share via

Data Factory Copy Activity - SYNC - Stored Proc - Has anyone used generic parameter to use same proc on multiple tables?

Your answer