Passing SQL file as a parameter to source dataset in ADF

Question

Passing SQL file as a parameter to source dataset in ADF

Purushottam Uppu 21

Hi,

I am using a SQL query to connect to Oracle database in my source dataset. But the query is a dynamic in nature and any changes to the query would end up modifying the query in source dataset and publish.

To avoid this, we wanted to place the query in a .sql file as a file and call this during ADF execution.

Is it possible? Appreciate your response.

Thanks
Purushottam

ShaikMaheer-MSFT 34,536 Reputation points Microsoft Employee

2021-07-05T06:42:28.627+00:00

Hi @Purushottam Uppu ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
Purushottam Uppu 21 Reputation points

2021-07-05T07:20:06.5+00:00

Hi, @ShaikMaheer-MSFT ,

Am trying to test the same but getting error.

My SQL query is lengthy and has 1500+ lines of code. Hence, am getting "Invalid sql statement" error.

Any suggestion, highly appreciated.

thanks
Purushottam
ShaikMaheer-MSFT 34,536 Reputation points Microsoft Employee

2021-07-07T05:03:15.653+00:00

Hi @Purushottam Uppu ,

Detailed answer shared below for follow up ask. Kindly check and Accept Answer. Accepting answer will help community. Thank you.
Siva 1 Reputation point

2022-11-28T12:49:45.567+00:00

I am also facing the issue and have implemented the workaround by tweaking the datasets. But the lookup activity is having a \r\n at the end of every new line.

For example if the query in sql file is

select
* from
sample

Then the lookup activity output is select \r\n*from\r\nsample even with the row and column delimiter in the dataset as '$'

Please provide your valuable suggestions to resolve the issue.

ShaikMaheer-MSFT 34,536 Reputation points Microsoft Employee

2021-07-05T06:42:28.627+00:00

Hi @Purushottam Uppu ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
Purushottam Uppu 21 Reputation points

2021-07-05T07:20:06.5+00:00

Hi, @ShaikMaheer-MSFT ,

Am trying to test the same but getting error.

My SQL query is lengthy and has 1500+ lines of code. Hence, am getting "Invalid sql statement" error.

Any suggestion, highly appreciated.

thanks
Purushottam
ShaikMaheer-MSFT 34,536 Reputation points Microsoft Employee

2021-07-07T05:03:15.653+00:00

Hi @Purushottam Uppu ,

Detailed answer shared below for follow up ask. Kindly check and Accept Answer. Accepting answer will help community. Thank you.
Siva 1 Reputation point

2022-11-28T12:49:45.567+00:00

I am also facing the issue and have implemented the workaround by tweaking the datasets. But the lookup activity is having a \r\n at the end of every new line.

For example if the query in sql file is

select
* from
sample

Then the lookup activity output is select \r\n*from\r\nsample even with the row and column delimiter in the dataset as '$'

Please provide your valuable suggestions to resolve the issue.

Answer 1

ShaikMaheer-MSFT 34,536 Microsoft Employee

Hi @Purushottam Uppu ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query here.

Please check below detailed explanation of implementing same.

Here, I am having my SQL query in "Query.sql" file on blob storage. Reading data from that "Query.sql" file using lookup activity. Using lookup activity output in copy activity query field dynamically.

Step1: Query.sql file in my blob storage

Step2: Lookup activity in pipeline to get our SQL query from file on blob

Step3: Lookup activity output json shows our SQL query from file.

Step4: Copy Activity. In Source using query option and dynamically running our query from lookup activity output.

Hope this will help. Thank you

--------------------------------------

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Purushottam Uppu 21 Reputation points

2021-07-05T07:11:31.4+00:00

Hi, This works, but my SQL query is lengthy and spread across 1500+ lines of code.
so, when I tried this approach, getting "Invalid sql statement" error.

Error code
9602
Details
ERROR [42000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00900: invalid SQL statement . Activity ID: c2399159-16f2-4e55-bd15-98fda33c1a98
ShaikMaheer-MSFT 34,536 Reputation points Microsoft Employee

2021-07-06T07:20:19.5+00:00

Hi @Purushottam Uppu ,

Thank you for follow-up query. Error might be because of multi lines of query. So, we need to twist dataset a bit so that our dataset will try to read all rows as single row.

In above provided answer I used csv type dataset in lookup activity. That same dataset I modified a bit so that all rows in sql file should be treated as single row.

SQL File in blob storage:

Dataset:
Use some symbol(which is not there in your query) as column and row delimiter. In my case I used ~ as column and row delimiter.

Please Accept Answer if this helps. Thank you.

Passing SQL file as a parameter to source dataset in ADF

0 additional answers

Activity