topic Producing Dynamic SQL in Dataflow in Pipelines

Producing Dynamic SQL in Dataflow

Anonymous — Mon, 11 Dec 2023 13:30:25 GMT

As we can't currently pass parameters from Pipeline to Dataflow in Fabric, a workaround is to access Dynamic SQL from a txt file.

We can use Table.FirstValue to read the first row of a txt file

let
DynSQLTable = Csv.Document(Web.Contents("https://DOMAIN.sharepoint.com/Documents/DynSQLTest.txt"), [Delimiter = "|", Columns = 1, QuoteStyle = QuoteStyle.None]),
DynSQLString = Table.FirstValue(DynSQLTable),
Source = Oracle.Database("SERVER:PORT/SERVICENAME", [Query = DynSQLString , HierarchicalNavigation = true])
in
Source

Re: Producing Dynamic SQL in Dataflow

Anonymous — Tue, 12 Dec 2023 12:40:47 GMT

Hi Anonymous ,

Thanks for using Fabric Community.

Are you sharing any information or asking any query.
With the above context I am not able to get a bigger picture of it.
Could you please share few more details of your scenario and about your issue?

Re: Producing Dynamic SQL in Dataflow

Anonymous — Tue, 12 Dec 2023 14:33:43 GMT

Hi,

I was just sharing a workaround for passing parameters into a Dynamic SQL query as we can't directly pass parameters to a Dataflow.

The snippet above was intended for anyone who wanted to do the same.

If you create a basic Gen2 Dataflow, and add a datasource....

then modify the code generated to read the Dynamic SQL from a file and parse it into the Query.

Re: Producing Dynamic SQL in Dataflow

Steve_B77 — Wed, 06 Mar 2024 13:10:38 GMT

Anonymous Very useful - thanks