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

Question

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

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

Vinodh247 34,666 Reputation points MVP Volunteer Moderator

2023-05-17T10:57:24.01+00:00

any specific reason to not use "Auto Create Table" option in your use case?
RogerPujolGrau-2941 45 Reputation points

2023-05-17T11:10:56.9966667+00:00

@Vinodh247 Because in the future the pipeline will be adapted to handle multiple types of data storages which not all suppport "Auto Create Table"

1 answer

Your answer

Vinodh247 34,666 Reputation points MVP Volunteer Moderator

2023-05-17T10:57:24.01+00:00

any specific reason to not use "Auto Create Table" option in your use case?
RogerPujolGrau-2941 45 Reputation points

2023-05-17T11:10:56.9966667+00:00

@Vinodh247 Because in the future the pipeline will be adapted to handle multiple types of data storages which not all suppport "Auto Create Table"

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

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

RogerPujolGrau-2941 45 Reputation points

2023-05-18T11:45:35.61+00:00

This is very helpful, thanks @AnnuKumari-MSFT ! I have one last doubt about the datatypes in the Create Table script. How do you fetch them from the origin table? Or how can I adapt it for the Copy Activity to work? The datatypes are not my priority as the copy of the data I'm doing is meant to be for Staging purpouses, so it might suffer modifications.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-13T09:21:44.4033333+00:00

Hi RogerPujolGrau-2941 ,Apologies for delay in response. The data type from source won't be coming along . If you want the schema same as source, then you need to explicitly create the sink tables by pre-defining the datatypes in the sink.

If it helps, kindly accept the answer. Thankyou

Share via

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

1 answer

Your answer