Data Connector import rule fails to remove a specific column's existing value when it is set to be blank

Products

• Symantec Products

Issue/Introduction

When using a Data Connector import rule, the data to import includes a blank (null) value for an existing record's column value. For example, a computer's System Number field is desired to be blanked by setting its value to null via the data file. This may look like the following:

Name, System Number, Comment
PC1,,This PC was changed on 9/1/2019

Where ",," is the blank System Number column value.

When the import rule runs, however, this fails to blank the specified column, instead skipping this and returning the error "x rows of data were skipped for data class '<column_name>'."

In the Connector Rule Rule Status window:

x rows of data were skipped for data class '<column_name>'.

In the Import Rule Log Viewer window:

Row x skipped for data class '<column_name>'. All columns contain null values.

Environment

ITMS 8.x

Cause

The column contains a blank value, which Data Connector ignores.

Resolution

This is working as designed. Data Connector is only designed to import data as valid characters. A blank or any number of only spaces, effectively making it null, is not a valid character(s) to import.

There are two ways to change this functionality:

1. If all values for all records for the specified column are all to be removed, this can be set in the "Data class mappings" section of the Import Rule:
   
   a. In the import rule, in the "Data class mappings" section, find the data class mapping to change.
   b. Change its Source from the data column name to "<Null>".
   c. Run the import rule. This then removes all values for the specified column and sets them to null.
    
2. Or, if only some values for some records of the specified column are to be removed, a temporary "flag" value can be added that can then later be changed to null by using a SQL script:
   
   a. In the data file used, change the columns that are null to a flag value, for example, "changethistonull". Note: Ensure that whatever the flag value is, that the data class itself can use this as a valid value, otherwise a different error will occur stating that this is an invalid value to set it to.
   b. Run the import rule normally. The specified columns for the specified rows then have their values set to the flag value.
   c. In SQL, the user will have to know what tables and data classes contain the flag value, and then, using a SQL script, change these to be null. If the user is unsure which tables and data classes these are, a search script.
   
   d. Once the tables and data classes are found, run a SQL script to set these to null. For example:
   
   USE Symantec_CMDB
   UPDATE <table>
   SET <column> = ''
   WHERE <column> = '<flag_value>'