CODEWITHSUNDEEP
azure-data-factory
Maverik
Maverik

Reputation: 421

Getting data from multiple tables into one documentdb collection using adf pipeline

I am trying to pull data from multiple tables in on-prem server to a single collection in DocumentDB using SQL inner joins. Do i need to create dataset for each table in SQL database? Can I have multiple inputs in pipeline, in case I need to create an independent dataset for each table?

"inputs": [
                {
                    "name": "SQLServerDataset-Table1"
                },
                 {
                    "name": "SQLServerDataset-Table2"
                }
            ],

Upvotes: 1

Views: 462

Answers (1)

wBob
wBob

Reputation: 14379

This article states the following:

You can query over multiple tables within the same database using a single dataset, but a single table must be used for the dataset's tableName typeProperty.

Set the SqlReaderQuery property in the pipeline, something like this:

"typeProperties": {
              "source": {
                "type": "SqlSource",
                "SqlReaderQuery": "$$Text.Format('SELECT a.col1, b.col2 FROM  tableA a INNER JOIN tableB b ON a.col1 = b.col1 WHERE a.timestampcolumn >= \\'{0:yyyy-MM-dd HH:mm}\\' AND timestampcolumn < \\'{1:yyyy-MM-dd HH:mm}\\'', WindowStart, WindowEnd)"
              }

I haven't tested this but the above article shows how to do it. Let me know if it doesn't work.

Upvotes: 0

Related Questions