Troubleshooting import issues when using a Data Connector import rule

Products

• IT Management Suite
• Client Management Suite
• Software Management Solution
• Asset Management Solution

Issue/Introduction

Many types of issues and errors can occur when trying to import data using a Data Connector import rule. This article provides best practices for using import rules and information on how to troubleshoot them.

Environment

ITMS 8.x

Resolution

Best practices and troubleshooting tips on how to create a working import rule

The data file

• A data file is commonly used with import rules, but databases can also be used. A data file can be in an Excel (.xls) or Comma Separate Value (.csv) file format.
• If using an Excel file, ensure that the cells that contain data do not contain non-standard or unexpected characters. For example:
  
  * Trailing spaces, such as a serial number that ends with extra spaces "ABC123       " but shouldn't. Note: Trailing spaces are not apparent when looking at cells. Enter the specific cell and then go to the end of its data to identify if trailing spaces exit or not.
  * Invalid XML characters, such as the ampersand character "&".
  * Formulas. In most cases, an import rule can derive the data successfully from a cell's formulas. If issues occur due to these, however, it is recommended to try using the data directly. If this works, then the special formatting of the cell is the issue and should be avoided if possible. Note: Formulas are not apparent when looking at cells. Click on the cell to see if there is a formula present, which will then appear in the top formula bar of the window.
  
  
• If Excel data appears to be an issue, export it's data to a .csv file and try this instead. If this works, then the Excel's formatting of the cells was the issue. 
• For any file type, when possible, include column names, especially ones that match the data class or association names they will map to in the import rule. 
• When making a data file, start simple. Add in only a few test records and use the import rule's "Test rule" button to verify if these work before adding more records.
• Use the same method for troubleshooting data. If many records are used (hundreds or thousands), remove most and leave only perhaps a few or up to fifty or a hundred: does the import then work? If so, then the issue is with the removed records. It may be necessary to systematically re-add or remove blocks of these until certain records are found that are the issue. Once found, compare the failing records with those that work: what's the differences? Something that the failing records are doing or not doing is likely the issue.

The data source

• The data source references the data file or database that the import rule pulls data from to then import.
• If "pre-processing import data" is to be used, only enable this after the import rule has been later tested to work successfully. If issues occur later when using pre-processing, disable this to re-verify if this is the issue or not; if so, perform troubleshooting on the C# code. Information on using C# code with a data source can be found in this article: 
  
  181197 Using C# code in Data Connector data source pre-processing
   
• Use a unique data source for each import rule. The data file, however, can be shared between multiple data sources if needed. 

The import rule

• Ensure that the data mappings and associations (if any) are configured correct and as expected. Otherwise, data may import incorrectly or be skipped. For example, if a field is not being set to the expected column but is being set to another column, it is likely that the column mapping for this was incorrectly set.
• When making an import rule, start simple. Add in the most important data classes first and then use the "Test rule" button to verify if these work before adding in more complex mappings, e.g., associations, custom data classes or custom resource lookup keys.
• Use the same method for troubleshooting an import rule. Remove more complex mappings: does the import rule then work? If so, then the issue is with the removed mappings. It may even be necessary to systematically remove every mapping, complex and basic, to finally find the area with the issue.
• If a "data filter" is to be used, only enable this after the import rule has been later tested to work successfully. If issues occur later when using data filtering, disable this to re-verify if this is the issue or not; if so, perform troubleshooting on the SQL script.
• When possible, use the "Test rule" button to test the import before using the "Run now" button. Also when possible, bring in only small test amounts of data first, and even better yet, on a test server. Warning: Importing in large amounts of data can be difficult to undo if performed incorrectly, and therefore it's always recommended to be caution when setting up an import rule.
• It is strongly recommended to use the import rule's log to troubleshoot issues. This should not be confused with the Altiris server logs (the a.log files), which often don't actually include useful troubleshooting information. The import logs can be found on the Connector Rule Run Status window and in the "Run history" section at the bottom of an import rule:
  
  
  
  
  
  Import logs can also be found as .xml files on the Altiris server's hard drive, which can be sent to Technical Support as needed.
  
  By viewing the import log, errors that occur on specific records can be viewed and troubleshooting performed. For example, the following error shows that the required resource name for record 1 wasn't found and was therefore skipped.
  
  
  
  Note: If the import rule is not logging, turn on verbose logging to change this. Instructions for this can be found here:

• For more information on creating import rules, including data files and data sources, refer to the following article:
  
  181160 Using Data Connector to import data into the Symantec Management Platform database 

General usage troubleshooting

• When using the "Select data classes" link, custom data classes will not appear for selection if they have not been assigned to the resource type. This is because custom data classes cannot be used individually, they require to be part of a resource type to be usable.
• If data columns were changed (renamed, added or removed) from the data file, the import rule will no longer work correctly and will result in an error because it is still coded to attempt to access these when it opens or runs. For example, "This run failed with an error: Failed to get resource <GUID>." To resolve this, point the import rule to use a different data source then change back to the original data source. This will refresh what data classes are to be used but will unfortunately have the side effect of requiring the user to set up the data classes and associations it uses. Likewise, if new columns were added, the data source and import rule won't immediately be able to use these unless they are refreshed.
• If an import rule was copied from one server or another, or, if custom data classes, resources or associations were present that the import rule used but now are not, a "missing" or "cannot find" GUID error will appear at the top of the import rule it is opened. Saving the import rule will resolve this but without its prior mappings for the missing customization(s). The preferred method of resolving this is to add the missing customization(s), then delete and re-import the import rule. This will then detect the customization and open correctly. If not, then the mappings to the recreated customization(s) will need to be manually re-added by the user.
• If resource lookup keys are renamed, removed or mapped incorrectly from the data source, the error "The import rule '<import rule name>' failed. Error: The column '<column name>' required for resource lookup key 'Resource Name' is mapped to column '<column name>' that does not exist in the foreign data source." will occur. Ensure that these are present and configured correctly to resolve this error.
• In rare situations, the specific import rule may be corrupted. If everything seems like it should be working but isn't, create a brand new import rule and set it up the same way. If the new import rule works, delete the old corrupted one and use the new one instead. 

Specific import log error messages

Many different errors can occur when an import rule is tested or ran. These are viewable in the import log. Often, these are caused by the data not the import rule and once known, can be resolved by changing the data file. The following article describes these in more detail:

158141 Troubleshooting Data Connector import rule import log errors

Additional Information

158816 Checking if a custom CMDB rule or Data Connector import rule is scheduled

156751 Data Connector import rule fails to set a foreign key correctly

155139 Import Rule fails to set or removes Asset Owner or Cost Center

156087 Error: "Unable to find column definitions in the given xml document." occurs when trying to use an .xml file with a Data Connector Data Source

154132 Error: "Failed to load list of tables/sheets. The 'Microsoft.ACE.OLDEDB.12.0' provider is not registered on the local machine." occurs when trying to use Data Connector data source with an Excel spreadsheet