Is there a way to see the SQL query being sent to the SQL data source in Mapping Data Flow?

Question

Is there a way to see the SQL query being sent to the SQL data source in Mapping Data Flow?

Mathew Partridge 221

I have been running into an issue debugging parameterised sql data sources in MDFs when the query is highly parameterised or has embedded MDF expressions. I don't seem to be able to see the query sent anywhere. Does anyone know how I can see the SQL query that was sent?

Yes, I know it's in the Query box but I am talking about the query actually sent when it contains parameters or MDF Expressions.

I know I can create a dummy data source, add on a derived column and add the same paramterised/expression SQL query and then see the results but that requires me to either have a dead end branch or edit my MDF in order to debug the data source.

Is there an easy way to see the SQL sent to the DB in the MDF?

MarkKromer-MSFT 5,146 Reputation points Microsoft Employee

2021-06-25T17:53:33.59+00:00

You could use a query analyzer on the database side
Mathew Partridge 1 Reputation point

2021-06-25T18:02:01.553+00:00

Sadly that won't work. My team are basically end users of the DB and don't have permissions to do that. I'll check in with the team that manage it to see if it can be arranged but I think it is likely to be a no.

I'm assuming that the DB side answer says that you can't see the query from the ADF side? @MarkKromer-MSFT
MarkKromer-MSFT 5,146 Reputation points Microsoft Employee

2021-06-25T20:20:37.74+00:00

You can log it yourself in your dataflow using a derived column. Take the string that you use in your query property and paste it into a new column. Write that column to a file.
Mathew Partridge 1 Reputation point

2021-06-25T20:26:36.71+00:00

@MarkKromer-MSFT yeah I already mentioned that approach, that was in my original question as something I'm already doing, was looking for a way that didn't require me to edit my MDF to debug or have a dead end part of a production MDF.

MarkKromer-MSFT 5,146 Reputation points Microsoft Employee

2021-06-25T17:53:33.59+00:00

You could use a query analyzer on the database side
Mathew Partridge 1 Reputation point

2021-06-25T18:02:01.553+00:00

Sadly that won't work. My team are basically end users of the DB and don't have permissions to do that. I'll check in with the team that manage it to see if it can be arranged but I think it is likely to be a no.

I'm assuming that the DB side answer says that you can't see the query from the ADF side? @MarkKromer-MSFT
MarkKromer-MSFT 5,146 Reputation points Microsoft Employee

2021-06-25T20:20:37.74+00:00

You can log it yourself in your dataflow using a derived column. Take the string that you use in your query property and paste it into a new column. Write that column to a file.
Mathew Partridge 1 Reputation point

2021-06-25T20:26:36.71+00:00

@MarkKromer-MSFT yeah I already mentioned that approach, that was in my original question as something I'm already doing, was looking for a way that didn't require me to edit my MDF to debug or have a dead end part of a production MDF.

Answer 1

ShaikMaheer-MSFT 29,886 Microsoft Employee

Hi @Mathew Partridge ,

Thank you for posting query in Microsoft Q&A Platform.

There is no direct way to view your query sent to SQL. One work around would be as you said using create a dummy source and use derived column to store query.

Please feel free to log your feedback in below azure feedback portal. Azure data factory actively monitor feedbacks there and consider them to implement in future releases. 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 email-notifications.

Mathew Partridge 221 Reputation points

2021-06-29T12:55:41.377+00:00

@ShaikMaheer-MSFT I would love to be able to post another idea but unfortunately I am all out of ideas (and votes) so no more feedback for me:
ShaikMaheer-MSFT 29,886 Reputation points Microsoft Employee

2021-06-30T05:10:01.387+00:00

Hi @Mathew Partridge ,

Thank you accepting it. I will consider this item to forward as feedback to ADF team. Thank you.

Is there a way to see the SQL query being sent to the SQL data source in Mapping Data Flow?

0 additional answers

Activity