Azure Data Factory Input dataset with sqlReaderQuery as source

Question

We are creating Azure Data Factory pipeline using .net API. Here we are providing input data source using sqlReaderQuery. By this mean, this query can use multiple table. So problem is we can't extract any single table from this query and give tableName as typeProperty in Dataset as shown below:

"typeProperties": {
            "tableName": "?"
        }

While creating dataset it throws exception as tableName is mandatory. We don't want to provide tableName in this case? Is there any alternative of doing the same? We are also providing structure in dataset.

Answer 1

Unfortunately you cant do that natively. You need to deploy a Dataset for each table. Azure Data Factory produce slices for every activity ahead of execution time. Without knowing the table name, Data Factory would fail when producing these input slices.

If you want to read from multiple tables, then use a stored procedure as the input to the data set. Do your joins and input shaping in the stored procedure.

You could also get around this by building a dynamic custom activity that operates, say, at the database level. When doing this you would use a dummy input dataset and a generic output data set and control most of the process yourself.

Answer 2

It is a bit of a nuisance this property being mandatory, particularly if you have provided a ...ReaderQuery. For Oracle copies I have used sys.dual as the table name, this is a sort of built-in dummy table in Oracle. In SQL Server you could use one of the system views or set up a dummy table.