How to perform copy activity with new ServiceNow connector in ADF. I am getting below error while perform copy activity

Hari Tiwari, Tieto 0 Reputation points
2024-11-04T13:32:27.0166667+00:00

I am changing old legacy ServiceNow connector to new ServiceNow connector, In new ServiceNow connector we don't have any option to select table or query. for that I have selected table name in datasets. create one simple copy activity and try to copy data from ServiceNow to ADLS.

I am getting below error message while executing copy activity.

Failure happened on 'Source' side. ErrorCode=UserErrorTypeInSchemaTableNotSupported,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to get the type from schema table. This could be caused by missing Sql Server System CLR Types.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.InvalidCastException,Message=Unable to cast object of type 'System.DBNull' to type 'System.Type'.,Source=Microsoft.DataTransfer.ClientLibrary,'

same setup is working with old ServiceNow connections.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,828 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sina Salam 12,011 Reputation points
    2024-11-04T17:07:16.15+00:00

    Hello Hari Tiwari, Tieto,

    Welcome to the Microsoft Q&A and thank you for posting your questions here.

    I understand that you are having error while performing copy activity Azure Data Lake Storage (ADLS).

    The error you're experiencing is related to the type compatibility between the data in ServiceNow and the Azure Data Lake Storage (ADLS) target when using the new ServiceNow connector. This issue commonly arises when the connector encounters a DBNull value but expects a defined data type - https://video2.skills-academy.com/en-us/azure/data-factory/copy-activity-overview

    1. To resolve the type casting issue with the new ServiceNow connector, ensure that the data types in your ServiceNow table are compatible with the connector, and verify that the necessary SQL Server System CLR Types are installed. https://video2.skills-academy.com/en-us/azure/data-factory/connector-servicenow
    2. Check the schema mapping in your copy activity for any discrepancies, and implement null handling to manage DBNull values. https://video2.skills-academy.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping
    3. Double-check the configuration settings of the new ServiceNow connector, and refer to the official documentation for any specific requirements or known issues.
    4. You might need to contact Microsoft Azure Support from your Azure Portal or ServiceNow support for further assistance.

    I hope this is helpful! Do not hesitate to let me know if you have any other questions.

    Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

  2. Ganesh Gurram 1,025 Reputation points Microsoft Vendor
    2024-11-06T12:46:25.4133333+00:00

    @Hari Tiwari, Tieto - Thanks for the question and using MS Q&A forum.

    The new ServiceNow connector in Azure Data Factory (ADF) is designed to provide a more flexible and efficient way to interact with ServiceNow instances. However, it requires a different approach to data extraction compared to the older connector. 

    Below screenshot shows the difference between ServiceNow and ServiceNow (legacy): 

    User's image

    Key Differences and Considerations:

    1. The new connector doesn't directly support table selection or querying within the copy activity.
    2. Instead, you need to define a dataset that points to a specific ServiceNow table or a specific query. 
    3. The dataset should include the necessary authentication details (client ID, client secret, instance URL) and the specific table name or query. 
    4. The connector will extract data based on the specified dataset. 
    5. Ensure that the query or table name in the dataset is accurate and that the ServiceNow instance has the necessary permissions for data access. 

    Troubleshooting the Error: 

    The error message you're encountering suggests that the connector is unable to determine the schema of the data. This could be due to several reasons:  

    Double-check the dataset configuration to ensure that the table name or query is correct and that the authentication details are valid. 

    1. Verify that the ServiceNow instance has the necessary permissions to access the specified table or query.   
    2. If you're using a complex query or a large dataset, there might be limitations on the ServiceNow API side. 
    3. Consider breaking down the query into smaller, more manageable chunks or optimizing the query performance. 
    4. While the new connector is generally more flexible, it might have specific limitations or known issues. 
    5. Check the official Microsoft documentation for any known limitations or workarounds.   
    6. Ensure that the data format and schema of the ServiceNow data are compatible with the ADF pipeline and the sink data store. 
    7. You might need to use data transformation activities like data flow to clean, transform, and enrich the data before loading it into the target. 

    For more information refer: Copy data from ServiceNow using Azure Data Factory or Synapse Analytics

    Hope this helps. Do let us know if you have 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.  

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.