Auto create table not working

Question

Hi,

Is it possible to have a Pipeline sink to a SQL table that does not exist until runtime?

Reading the help bubbles it seems like this is possible. I can publish the data set without a table

but if I add a pipeline that uses this data set it throws an error.

Thanks

Error

Dataset

Pipeline

Answer 1

Hi Dave Gray ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As I understand your query, you want to create the sink table on the fly during the pipeline execution as the table does not exist in the sink beforehand. Please let me know if that is not the correct understanding.

You can definitely use AutoCreateTable option in the sink setting to create the table during the pipeline execution itself. However, you need to specify the name you want to give to the sink table inside the dataset.

You can either hardcode the sink table name by clicking on edit and providing the desired name to it, or you can parameterize the source and sink dataset so that when you want to run the pipeline for dbo.tableA , it copies dbo.tableA from source and load it into sink with the same name. You can use same pipeline to copy dbo.tableB by providing the parameter value as dbo.tableB during the pipeline execution.

You can also watch out this video to see the use case of auto create table option

For one table : https://youtu.be/dStqVShWNVY?t=857

For multiple tables: https://www.youtube.com/watch?v=GdOmOV2C1fs&list=PLsJW07-_K61KkcLWfb7D2sM3QrM8BiiYB&index=2

In the second case, sink is delimited text, but you can use the same concept to load the data to sql table.

---

Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well.