New ServiceNow Connector in ADF(Unable to pass the SQL query in Source side)

Question

Hi Team,

How Can I pass the SQL query in ServiceNow dataset while using ServiceNow as Source in ADF Copy Activity As New ServiceNow connector does not support SQL query as per Microsoft docs. It supported in legacy connector so I passed the SQL query directly to fetch the data from ServiceNow(Source).Now there is no option and I want to modify with the latest connector and it has to work as my old pipelines are working

Legacy connector screenshot:

New Connector Screenshot:

While looking both the screenshot you can clearly identify that there is no SQL query option pass the query in new ServiceNow connector but how can I pass and make the pipeline run while passing the SQL query. Hope my query is clear to all of you.

Please let me know if you need anything.

Many thanks

Pankaj

Answer 1

Hi @pankaj chaturvedi

Welcome to Microsoft Q&A platform and thanks for posting your query here.

Currently, the new ServiceNow connector in Azure Data Factory (ADF) does not support passing SQL queries directly, unlike the legacy connector.

Here are some potential workarounds to achieve similar functionality with the new ServiceNow connector:

1. The new connector supports data filtering using ServiceNow's native query syntax (encoded queries). You can utilize this option to narrow down the data retrieved, similar to using a WHERE clause in SQL.
2. If you require more flexibility, you might consider using the ServiceNow Table API, which supports custom queries and data extraction. You can integrate this API call within an ADF web activity or as a separate data pipeline step.
3. After fetching data with broader filters, you can use Data Flows in ADF to perform transformations and filtering, effectively replicating the results of your previous SQL queries.
4. For a more complex scenario, you may create pre-processing logic using Azure Functions, PowerShell scripts, or other tools to extract and prepare data before connecting to the new ServiceNow connector.
5. Another option is to move the data to an intermediate staging area (e.g., Azure SQL or storage) and apply SQL transformations there before proceeding with further data processing.

Since the new connector is still evolving, consider providing feedback to Microsoft regarding the need for SQL query support in the new ServiceNow connector. User feedback can influence future updates and enhancements to the connector.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.