question

MichaelShparber-6569 avatar image
0 Votes"
MichaelShparber-6569 asked GunaSekharVysyaraju-9212 commented

Create a target table on-the-fly in Data Factory

I am new to Data Factory.
I want to copy data from REST API into SQL Server tables.
Is there a way to automatically create SQL tables with data types based on the API calls?
I don't want to do this manually.
Thanks!

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChiragMishra-MSFT avatar image
1 Vote"
ChiragMishra-MSFT answered BryannaMartinez-2745 commented

Hi @MichaelShparber-6569,

You can Load data faster with new support from the Copy Activity feature of Azure Data Factory. Now, if you are trying to copy data from an any supported source into SQL database/data warehouse and find that the destination table does not exist, Copy Activity will create it automatically. After the data ingestion, review and adjust the sink table schema as needed.

This feature is supported with:

  • Azure SQL Database

  • Azure SQL Database Managed Instance

  • Azure SQL Data Warehouse

  • SQL Server

To automatically create a destination table, follow this path: ADF authoring UI > Copy activity sink > Table option > Auto create table. Or, click on the “tableOption” property in the Copy Activity sink payload. Please refer to the below screenshot for details :

10014-autocreate-table.jpg

Hope this helps. Stay safe !


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelShparber-6569 avatar image
0 Votes"
MichaelShparber-6569 answered

Awesome! Thanks @ChiragMishraMSFT-1092


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BobbyLansing-3640 avatar image
2 Votes"
BobbyLansing-3640 answered kabhyr converted comment to answer

When adding a SQL database it requires a table to be selected. Even with "Auto create table" selected I don't see data coming into the selected table or a new table being created.

How do I ensure that this creates a new table the first time it is run?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelShparber-6569 avatar image
0 Votes"
MichaelShparber-6569 answered kabhyr commented

I don't think it allows to Auto-create a table when the source is REST API

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

kabhyr avatar image
0 Votes"
kabhyr answered GunaSekharVysyaraju-9212 commented

I added a dummy table name in SQL Dataset by clicking on edit check box - dbo.TestEmployee

and then copy activity ran fine, it create a table with above name and inserted data.


Before: 118604-image.png



After: 118622-image.png



image.png (50.4 KiB)
image.png (50.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@kabhyr
lin your images, it looks like you were trying to load xml data to AzureSQL, correct ? I'm also looking to achieve the same
Were you successful in doing so ?

After adding dummy table in dataset, Debug activity throwing error saying "couldn't find the table" though I selected Auto-Create Option.

0 Votes 0 ·

same happening for me. My source is ADLS Gen2 .csv file and destination is SQL database

0 Votes 0 ·