A Guide to Troubleshooting Import Set Issues

[cesar_sandoval]

The official documentation describes the Import Set feature as

Import sets allow administrators to import data from various data sources, and then map that data into ServiceNow tables.

If money is king at banks, then data is king in the ServiceNow platform. In a bank, people use the ATM to make a single deposit. If they want to make several deposits at once, several checks, cash, and coins, for example, then they go inside the bank and a bank teller takes cares of this transaction. In the ServiceNow platform a single data addition (the “atm deposit” if you will) usually occurs when a user enters data into a form. For large data imports, the ServiceNow platform offers their own “bank teller” – the Import Set feature.

The purpose of this article is to introduce new users to this feature and gives them a blueprint on how to begin to troubleshoot issues that may arise while using Import Sets.

An Import Set Refresher

Let’s start with a quick refresher. I will use the bird’s-eye view of the Import Set to describe its main components:

Let’s go through this diagram from left to right – the order in which data travels through the ServiceNow platform. Immediately, you will notice that there are many option in which to retrieve the data. You may attach the data as a file, the ServiceNow platform can also pull the data from your database, or you can insert the data via web services. This is not the complete list of methods of importing the data into the ServiceNow platform. The complete list can be found in the official ServiceNow documentation.

The Data Source record is where the import properties are defined. In the data source record you specify import-specific details such as method of import, file type for attachment imports, database information when pulling information from a database server, or connection parameters if pulling information from an FTP server.

In the Data Source record, you also define the import set table (also known as the staging table). The import set table is where all the data is placed when imported. This table is meant to be used temporarily as is purpose is to hold the data before it gets transformed into their target tables. Since this data is temporary, the ServiceNow platform clears any contents that is older than seven days. Importing the data into the staging table completes the first of the two stages in this feature.

The second stage in the Import Set feature is the transform phase. This is the process of populating the target records from the data in the import set table. Most of the time, this is a one-to-one record mapping between the records in the staging table and the target table. However, the beauty of the transform map feature is that the administrator has full control of how to map the data that a one-to-one mapping is not always the case. It is also not a restriction to map to only one target table, the data from the import set table can be mapped to more than one target table.

In the previous few paragraphs, I summarized the complete Import Set feature by the steps in which data travels. An alternative way to summarize the feature is to examine all the tables in the ServiceNow platform where Import Set data is stored:

This diagram is separated by three types of tables. The tables that store information for scheduling imports, the tables that store information on the Data Source and Transform Map configuration, and the tables that store the actual data and information during run time. This diagram should come in handy when troubleshooting import set issues. There is a tool that saves time by automatically gathering all information from these tables that pertain to an individual import set. More information on this tool will be provided further down this article.

Issues That May Occur During an Import Set

The most common types of issues that the ServiceNow support organization receives around Import Sets can be grouped as follows:

• Issues during the import phase
• • The ServiceNow platform inability to connect to 3rd party to retrieve the data
  • Errors during the import phase
  • Imported data does not meet expectations (missing or unexpected data retrieved from 3rd party)
• • Issues during the transform phase
• • Data in target table does not meet expectations (missing or unexpected data in target tables after transform)
  • Errors during the transform phase
• • Performance
• • Either the import or transform of the data is taking too much time to complete

Let me begin with the first two types, then I will address performance issues. The very first thing that a Support engineer does when troubleshooting an Import Set issue is to “cut the issue in half”. That is, determine if the issue occur during the import phase or the transform phase. You do this by checking the data in the import set table (staging table) and determine if is a replica of your source data and that the import completed without any warnings or errors. If the import is successful, then you can eliminate the whole left side of the diagram:

However, if the import had warnings, errors, or the import set table content does not match the source data, then focus should be placed on the left side. In other words, if you had issues during the import phase, then there is no reason to look at the transform configuration since the data handed to the transform phase is already “bad”. See below diagram:

Now that we found out that a complete and successful import phase is the first and one of the most important things to verify, let’s find out how to determine if the import was successful. You can start by looking at the following three items:

• Does the total number of imported records match the total number of records in the source?
• Is the Import Set’s State value is “Loading” and the number of imported records has not increased in a while. Note: “a while” depends on the import. For example, if the Import Set state is set to “Loading” and the number of imported records has not increased in a couple to a few minutes, then I know something went wrong causing the import to “stop” or “get stuck”
• Check for warnings or errors in the Import Log [import_log] table.

If the total number in the import set table matches your expectation AND there were not warnings or errors in the Import Set table, then you can safely assume that the import phase was completed successfully.

Let’s say you are troubleshooting an Import Set issue and you have ruled out the import phase as the culprit. It is now time to focus on the transform phase. Determining transform issues is a bit more complicated than finding issues during import because there is a lot more flexibility during transform than imports. Administrators can mold the raw data in such a way before it is moved into the target tables that it becomes undistinguishable from the import set data. Since the target table content depends heavily on the business logic ServiceNow support relies on the customer to determine what is expected or not expected. Nevertheless, here are some tricks to quickly check if the transform went wrong:

• Every record imported has been processed by each of the transforms associated to the Data Source. You can verify this by validating the ‘State’ field of every Import Set Row [sys_import_set_row] and make sure none is set to either ‘Error’ or ‘Pending’.
• Check for warnings or errors in Import Log [import_log] table.

I just described a high-level process on how to troubleshoot non-performance Import Set issues. There is a knowledge base article found at https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0541631 that contains the top questions and solutions around Import Sets. I strongly recommend you review the articles in this resource to help you troubleshoot Import Set issues.

The third group of Import Set issues deals with performance. Many of the cases that our organization gets regarding Import Sets has to do with “slow” imports. The three main things to keep in mind when troubleshooting Import Set performance issues are (in order):

1. Just stating “my import is slow” or “my transform is slow” does not suffice. The rate in which records are imported and transformed is a much better indicator than the total time. If you use the total time, then you MUST take in consideration the total number of records. For example, 1 hour is OK if you are processing one hundred thousand records, however 1 hour is slow if you are processing 100 records.
2. As with the aforementioned first two types of Import Set issues, it is crucial to determine whether the import is slow or the transform is slow. Very seldom both, the import AND transform, phases are slow and when this happens the cause is usually a performance issue with the whole instance and not the Import per se.
3. Finally, we must make sure the expectation is correct. For example, importing 100 records per second or transforming 20 records per second is usually good. If your rate is around this time, then there may be nothing that can be done to increase the rate. Note that the above rate is simply for demonstration purposes and a “ballpark” figure of what is OK. Since every Import Set is different, it is difficult to provide a baseline on what is expected as good vs bad rate.

Import Set Troubleshooting Tool

Thankfully, there is a tool that will summarize all the important information on an individual Import Set. Run the Import Summary Tool script in KB0870045 (https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0870045) to receive the following:

• Import Set Definition information:
• • Whether there is a scheduled job associated to this import. If there is, how often is it scheduled to run and the user that the job runs as.
  • The method in which data is imported and information on the import source.
  • The table where data is imported (the import set table)
  • The transform map (or maps) associated to this import
  • For each transform map, the target table, field maps, and any scripts that are associated to the transform map
  • Warnings if the script detects a configuration that may slow down the transform map.
• • Import Set Execution information:
• • The current state of the import
  • Timestamps when the import phase started and completed
  • Timestamps when the transform phase started and completed
  • Total number of records imported and transformed by each transform map
  • Total time and rate of import
  • Total time and rate of transform
  • Warning if the script detected the transform phase “stopped” or is “stuck”
  • The transaction that handled the job (when available)

With this tool you can quickly get all the information regarding a single import set.  Use this tool to diagnose import issues. If the information provided by this tool does not give you the information you need to find your issue’s root cause, then you can provide the script’s output to support when you open a case.  Including this information in your case for an Import Set issue will dramatically reduce the time for ServiceNow support to get up to speed with your issue.

Instance Troubleshooter Import Set Checks

If you are a ServiceNow administrator that is in charge for keeping a healthy set of imports. There is another tool that can benefit you. This is the Instance Troubleshooter tool. The Instance Troubleshooter is a ServiceNow application that scans all your import information and notifies you if it detects issues. I am not going to go into detail here, for a complete rundown see KB article “Instance Troubleshooter User Guide” at https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0870978

What I do want to point out here are the individual checks that this tool performs around the Import Set feature. The check is the individual pitfall that the tool searches for. Here is a list of selected checks performed by the tool specific to Import Sets:

• Import Sets with no records in import set table. This may be an indication that an error caused no records to be imported.
• Import Sets with no records processed. This may be an indication that an error prevented records from being transformed.
• JDBC or LDAP Data Source imports without a running MID Server. These types of Data Sources require a running MID Server.
• A valid connection between the instance and a third-party server is required for imports that retrieve data from a third-party server.
• A file Is required for imports that retrieve data from a file attached to the Data Source record.

We expect for administrators to use this tool to help them troubleshoot issues. In the next version, administrators will be able to schedule scans so soon the issues will be found before the user. There are several more checks in development that will make the Instance Troubleshooter tool even better at detecting issues with Import Sets.

I hope that this information helps you in keeping a healthy set of imports in your instance. Of course, you can always contact ServiceNow technical support if you have any questions.

Cesar Sandoval,
Technical Support Architect

[VaranAwesomenow]

Thanks for the article, I have found another valid scenario can occur where a data source returns data with no records in that case the steps that are needed to fix it is documented in below blog

https://community.servicenow.com/community?id=community_blog&sys_id=a6fe65b41bfd1d18c790ece6b04bcbf0

It is observed that when a data source of type REST (IntegrationHub) returns an attachment with no records such as below

[]

then ServiceNow will leave the import sets in loading state.

In this case response via REST API is valid and returned a 200 response, but there are no records to provide in response to the GET request hence record details are empty.

In order to handle this in scheduled import post script run the following script, this will verify if there are any records in import set row for the import set that is just created by the scheduled import and there are no rows then it marks import set to canceled instead of leaving it in loading.

//reference : https://docs.servicenow.com/bundle/tokyo-platform-administration/page/administer/import-sets/reference/r_DataImportScriptingOptions.html
//https://developer.servicenow.com/blog.do?p=/post/training-glideagg/
//Check if import set is in loading state and number of records are 0 then set importset state to canceled.
var isetSysId = import_set.sys_id;
var isetState = import_set.state;
var enQuery = 'sys_import_set=' + isetSysId;
    var aggISetRow = new GlideAggregate('sys_import_set_row');
    aggISetRow.addAggregate('COUNT');
    aggISetRow.addEncodedQuery(enQuery);
    aggISetRow.query();
    // Process returned records
    while (aggISetRow.next()) {
        var recCount =""; 
        recCount = aggISetRow.getAggregate('COUNT');
        //gs.loggingstatement("isetSysId=" + isetSysId + " count = " +recCount + " query = " + enQuery,'av'); - changed method name if logging needed change it to gs.log
        //current.state = 'Pending transform';
        if(recCount==0 || JSUtil.nil(recCount)) {
            setImportSetToCanceled(import_set.sys_id);
        }
    }

function setImportSetToCanceled(iSetid) {
    var grISet = new GlideRecord('sys_import_set');
    grISet.get(iSetid);
    grISet.state = 'cancelled';
    grISet.setWorkflow(false);
    grISet.update();
}