CODEWITHSUNDEEP
azureazure-data-factorydataflow
Grevioos
Grevioos

Reputation: 405

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!

Upvotes: 2

Views: 2660

Answers (1)

Leon Yue
Leon Yue

Reputation: 16401

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.

Upvotes: 4

Related Questions