Search code examples
azureazure-data-factorydataflow

ADF Mapping Data Flows, is it possible to execute SQL on a source?

So I continue to rewrite my lovely SSIS packages to ADF Data Flows. However, there is a lot of cases where I have some OLE DB Source with quite complicated SQL statement followed by other transformations.

Let's say there is a SQL statement which joins 10 different tables. As far as I know I can execute SQL statement only on my sink. So to get the very same dataset that is being used later, I have to create 10 different sources and 10 join operations. Is that correct?

It is possible but it doesn't seem to be very efficient. The only other thing that comes to my mind is to re-think our whole DWH logic but it would be a lot of added work, so I would rather avoid that.

Thank you in advance!

Solution

  • Actually, it's possible to execute SQL query on Source(only can do sql query).

    For example, I do a SQL query in Source Azure SQL database.

    Here's the data in my table test4 and test6:

    enter image description here

    Don't specify the table in Source dataset:

    enter image description here

    Data Flow Source setting:

    enter image description here

    Source Options, execute a SQL query joined two tables:

    select a.id, a.tname,b.tt from test6 as a left join test4 as b  on a.id=b.id

    enter image description here

    Import the schema of the query result:

    enter image description here

    Data Preview: enter image description here

    Hope this helps.