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

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 .

Passing SQL file as a parameter to source dataset in ADF

Accepted answer

ShaikMaheer-MSFT 38,466 Reputation points Microsoft Employee

2021-07-02T07:21:22.683+00:00
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.
Please sign in to rate this answer.
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 38,466 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.

Swathi Nareddy 0 Reputation points

2024-09-10T06:27:52.41+00:00

@ShaikMaheer-MSFT I have multiple queries in script file along with USE DB like below.. how can I achieve that. USE [devdb]

GO

INSERT INTO [dbo].[SN_TEST1]

([SNNAME] ,[PIPELINERUNID] ,[PARENTPIPELINEID] ,[BatchJobLogID]) VALUES ('test1', null, null, 1) GO

INSERT INTO [dbo].[SN_TEST2]

([SNNAME] ,[PIPELINERUNID] ,[PARENTPIPELINEID]) VALUES ('test2' ,null , 1)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Passing SQL file as a parameter to source dataset in ADF

0 additional answers

Your answer