Data Factory Copy: Auto-Create Sink Table

Question

In the Data Factory Copy feature, the Auto-Create option should create the destination table automatically. Yet, the Copy feature expects the table to already exist.

What I would like to do is:

1. Create Copy Step
2. Use a query in the Source tab
3. Specify the SQL Server as the Sink
4. Have option to auto create table without having to select an existing dataset or table

This is a standard option across other importing tools such as SSMS Import Wizard.

Answer 1

Hello @David Cruz ,

Thanks for the question and using MS Q&A platform.

As we understand the ask here is that you would like to auto create a table in your sink server without selecting the existing dataset or a table. Please correct me if I'm wrong.

I see that you have also commented here regarding the same here - https://learn.microsoft.com/en-us/answers/questions/471851/data-factory-auto-create-table-in-copy-activity-do.html

Just to clarify, the current behavior of auto create-table option in ADF copy activity is that Even with "Auto create table" option selected, the Sink settings expects a dataset to be defined with a successful linked service connection to the server and auto-creating table name should be provided in the dataset settings (irrespective if it already exists or not).

For auto-table creation enabled copy operation, when the copy operation executes it will check the target server if a table already exists with the name provided in the dataset settings, if it doesn't exist then it will auto-create a table with the name provided in the dataset (this is the reason it is required to specify the desired table name in dataset), and in case if the table already exists, then it will just insert/copy records into the table.

Please note that if you don't provide a table name in the dataset settings, then ADF will not know what to have the table name (as it is not specified) and it will throw a validation error.

Hope this clarifies. But if you have any additional feedback to improve the product behaviors, kindly share your ideas in Azure IDEAS forum here: https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c All the feedback shared in this forum are actively monitored and reviewed by respective product owners. Also please do share the feedback link once it is posted.

Hope this info will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

I also expected that ADF would be able to automatically use the source schema.tableName if not specified for "auto create table".