Business Adapter ignore null values - rows found with rejection messages

[kevin_christens]

I have Business Adapters that load data from CSV files and some of the values in the CSV are null but we don't want to have the adapter reporting it as an error.

I tried setting  'Advanced Properties' => 'If value is missing'(OnMissingFieldValue) set to:

"Remove the property from the import"

"Do nothing. (The import will fail.)"

I still get "Rows found with rejection messages" in the Summary of the System task log.

Is there a way to have no rejection messages if the value is null?

[ChrisG]

A specific answer would depend on which property you are working with here, and what you are using it for. However in general it is not typically feasible to configure adapters to operate such that rejection messages never appear.

A common example is where a column from your input data is used to look up existing records in FlexNet Manager Suite. If any rows in the input data contain a NULL value in the column, a rejection message will be logged indicating that no existing record could be looked up. In most situations that would be quite OK - i.e. the rejection message on its own does not necessarily indicate anything bad or unwanted happened.

[kevin_christens]

A null value on lookup, for example for an asset that no longer has an assigned user because that device is either a shared server or has been put into storage, is a valid value.  We want to null out the assigned user of assets that are no longer assigned but this raises a rejection record.   Is there a way to conditionally raise the rejection so it's not raised for "valid" null values?

[ChrisG · Accepted solution]

A failure to lookup in one object (resulting in a rejection event being logged due to the failure) shouldn't stop a property on a subsequent object being cleared - that should be happening (assuming the appropriate rule has been selected on the target property in the adapter to allow it to be cleared). If that is isn't working for you then it sounds like something else is going on here.

Short of configuring the adapter to log no rejection records, I can't think of any simple setting to avoid the rejection record being logged when a NULL value is provided for a lookup: these messages are expected to appear to indicate that the lookup could not be done, but the message should not be taken to indicate anything more than that - for example, don't interpret the messages as definitely indicating your source data was bad or the adapter implementation is inappropriate for what you're trying to achieve.

With that said, a couple of "advanced" approaches to perform lookups that would not result in rejection messages for NULL values being logged to consider might be as follows:

Rather than bringing in NULL values from the source data, consider bringing in a non-NULL value that you are sure will never match the value to be looked up. For example, a source query reading data from a CSV file that does this might look like:

SELECT IIF(ISNULL(EmpNo), 'This employee number does not exist', EmpNo) AS NoNullEmpNo, * FROM [UserData.csv]

Alternatively, if you are using FlexNet Manager Suite On-premises then a custom SQL query could be used to implement the lookup logic that you want instead of using the built-in capabilities a business adapter has to do a lookup. A typical pattern for this is:

1. In the source query, include an INT column that will be subsequently populated with the lookup results. For example, when querying from a CSV file the query might look like:
   

   SELECT *, -1 AS UserID FROM [UserData.csv]​

2. Use a custom object query in the adapter to perform the lookup needed and populate the column. For example:
   

   UPDATE i
   SET UserID = u.ComplianceUserID
   FROM #ECMImport_MyImportName i
       LEFT OUTER JOIN dbo.ComplianceUser u ON u.EmployeeNumber = i.EmpNo

[kevin_christens]

Chris,

You rock.  This is a perfect solution.  Only issue I'm having is that when I save the Business Adapter with a custom SQL, it removes the File Name from "CVS File Properties" section.   I also am not able to keep the setting of "Auto-generate SQL Query" unchecked.

Your recommendation of using the IFF SQL command works well.

[ChrisG]

Yeah, the behavior where the CSV filename is clobbered is unfortunate sorry. I don't know of any workaround to that short of continuously going back to the adapter properties page and fixing it.

For reference, this is listed as the following known issues (which may be duplicate entries for the same issue - I'm not sure):

IOJ-1757306: Business Adapter Studio may replace .csv file name with complex query text

IOJ-2184208: File Name field value is not saved by the Business Adaptor Studio for adapters using a CSV data source