In Azure Data Factory pipeline, how to parametrize a copy of SQL tables metadata and content with Copy Activity and no Auto Create Table?

RogerPujolGrau-2941 45 Reputation points
2023-05-17T10:01:05.17+00:00

Hello,

I'm trying to build a pipeline which copies all the tables from a SQL source and inputs them into another SQL sink, using Copy Activity and without using the "Auto Create Table" option. All using parametrization.

I am struggling creating automatically the tables where I want to input the data.

Any recommended approach?

Thanks

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,177 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 32,161 Reputation points Microsoft Employee
    2023-05-17T17:08:30.9366667+00:00

    Hi RogerPujolGrau-2941 ,

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    As I understand your scenario, you want to copy multiple tables from sql without auto creating the table at the sink. Please let me know if that is not the requirement.

    In order to parameterize the pipeline to copy multiple tables, you can store the source table metadata in a control table to store tablenames, schemanames, create table script for the table etc.

    Check the details on creation of control table in this video: How to do full load from On Premise SQL Server till ADLS using Azure Synapse Pipelines You need to add create table script in one column

    Now, To copy data from a SQL source using Copy Activity without using the "Auto Create Table" option, you need to create the destination tables manually. You can use the pre-copy script in the copy activity which uses the 'create table' script from the control table .

    Note: Write the sql query in such a way that it would create the table only if the table does not exist.

    Hope it helps. Kindly accept the answer if it's helpful. Thankyou