Data Factory auto create table in Copy activity doesn't seem to work, or isn't very useful

Question

Data Factory auto create table in Copy activity doesn't seem to work, or isn't very useful

Cornel Verster 46

Hi there

I'm trying to create copy activities where the source table is replicated into the Sink database, and the table is created according to what is in the Source. I know there is the "Auto create table" option when making the copy activity, but when I check this, it still tells me that I need to select a Sink table. This is exactly what I'm trying to avoid, as I want it to replicate the Source table in my Sink database, and I would expect that is what auto create table is supposed to do?

How do I get the copy activity to replicate the Source table without having to create the table manually in my Sink database?

Kind regards

KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-14T22:28:40.777+00:00

Hi @Cornel Verster ,

Just checking in to see if the below suggestion from anonymous userChaudhari was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread.

And, if you have any further query do let us know.
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-15T19:46:06.167+00:00

Hi @Cornel Verster ,

We still have not heard back from you. Just wanted to check if the below suggestion was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread.

And, if you have any further query do let us know.
Mohammad Shafiullah 0 Reputation points

2024-07-19T14:26:36.8566667+00:00

This is still a problem. I am copying multiple tables using for each activity from Source to Destination...in Microsoft Fabric. (Lakehouse to DWH). I tried emptying the table or droping each table before writing to destination table... (DROP / DELETE FROM / Truncate and FULL Load)

if the Source table in Lakehouse is empty and "Autocreate" table is checked, the following error is reported.....

ErrorCode=MissingSchemaForAutoCreateTable,

'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,

Message=Failed to auto create table for no schema found in Source.,

Source=Microsoft.DataTransfer.TransferTask,'

Answer accepted by question author

1 additional answer

Your answer

KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-14T22:28:40.777+00:00

Hi @Cornel Verster ,

Just checking in to see if the below suggestion from anonymous userChaudhari was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread.

And, if you have any further query do let us know.
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-15T19:46:06.167+00:00

Hi @Cornel Verster ,

We still have not heard back from you. Just wanted to check if the below suggestion was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread.

And, if you have any further query do let us know.
Mohammad Shafiullah 0 Reputation points

2024-07-19T14:26:36.8566667+00:00

This is still a problem. I am copying multiple tables using for each activity from Source to Destination...in Microsoft Fabric. (Lakehouse to DWH). I tried emptying the table or droping each table before writing to destination table... (DROP / DELETE FROM / Truncate and FULL Load)

if the Source table in Lakehouse is empty and "Autocreate" table is checked, the following error is reported.....

ErrorCode=MissingSchemaForAutoCreateTable,

'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,

Message=Failed to auto create table for no schema found in Source.,

Source=Microsoft.DataTransfer.TransferTask,'

Answer 1

Vaibhav Chaudhari 39,001 Volunteer Moderator

Open the Copy activity > Sink > Dataset > Table > Edit > type in the table name that should get created and run the pipeline.

Table that doesn't exist should get auto created

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Cornel Verster 46 Reputation points

2021-07-16T08:12:26.697+00:00

Hi there

I've tried this but it doesn't work. If I select no table it tells me to select a table, and if I enter a table name it tells me there is an error, so I end up having to create the table with all correct columns anyway.
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-21T20:04:35.203+00:00

Hi @cornelverster-949,

Apologies for the delayed response and sorry for your experience.

I just tested the auto create table feature and looks like it is working as expected. Please see below sample GIF.

I assume there might be some configuration issue. It would be great if you can share the complete error message with any images of the config of your dataset as well as your Copy activity. This would help us to troubleshoot and assist better.

We look forward to your response.

Thank you
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-22T18:06:48.583+00:00

Hi there,

We still have not heard back from you. Just wanted to check if you are still facing the issue or need assistance on this? In case If you already found a solution, would you please share it here with the community?

Thank you
David Cruz 26 Reputation points

2022-03-15T21:43:24.717+00:00

I'm not sure this was the solution. The AutoCreateTableEmp in your example seems to have already existed in the destination database. The question was how to get the Pipeline to automatically create the table on the first run.
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2022-03-16T06:40:09.937+00:00

Hi @David Cruz ,

Thanks for your comment. I see that you have opened a new thread on the same topic. Hence sharing the link here so that others can follow the discussion.

Linked Thread : https://learn.microsoft.com/en-us/answers/questions/773632/index.html
vaheed khan 5 Reputation points

2023-03-08T14:58:53.0033333+00:00

Is there a way to handle auto incremental primary key column while choosing auto-create table?
Mike Langlois 15 Reputation points

2024-05-21T14:24:44.7066667+00:00

Hi there, sorry I know this is very old, but this doesn't work any more. It returns an error 'Object does not exist or is not authorised'
Smaran Thoomu 35,040 Reputation points Microsoft External Staff Moderator

2024-05-22T06:25:52.71+00:00

Mike Langlois Since this thread is too old, I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.

Answer 2

Saf Sied 6

To add to what he suggested. When you open the Copy activity > Sink > Dataset > Table > Edit > there will be two text boxes in front of Tabe. Make sure in the first box you enter schema name (usually default schema dbo) and in the second box you enter your table name:

0 comments

Share via

Data Factory auto create table in Copy activity doesn't seem to work, or isn't very useful

1 additional answer

Your answer