ReadContainerImages reader import failure [FNMS On-Prem 2020 R1]
ReadContainerImages reader may fail import long HardwareValue values with error: "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column"
The Containers reader uses data from HardwareValues to write data into ImportedContainerInstance. The HardwareValues.Value field is a nvarchar(max) where as the fields that this data is written to during the execution of the reader is nvarchar(256). In the situation where the strings in HardwareValues are longer than 256 characters the reader will error. There are 6 fields in ImportedContainerInstance can be affected by this issue:
ComplianceReader logging from a failed import due to this problem will show output similar to the following:
2020-07-09 01:23:21,030 [INFO ] ReadContainerImages (Transfer data from source 'FlexNet Manager Suite' to FNMP) (VersionFrom: 14.3) 2020-07-09 01:23:21,129 [INFO ] Transferred 68 records 2020-07-09 01:23:21,129 [INFO ] Successfully processed in 0 seconds. 2020-07-09 01:23:21,130 [INFO ] ReadContainerInstances (Transfer data from source 'FlexNet Manager Suite' to FNMP) (VersionFrom: 14.3) 2020-07-09 01:23:21,276 [INFO ] Failed to execute Reader 'ReadContainerInstances' from file \\batchsvr\batchdata\Flexera Software\Compliance\ImportProcedures\Inventory\\Reader\managesoft\Container.xml, at step line 1 Error: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. 2020-07-09 01:23:21,276 [INFO ] All retries have been attempted for Reader 'ReadContainerInstances' 2020-07-09 01:23:21,393 [INFO ] Completed with error in 0 seconds. 2020-07-09 01:23:21,393 [INFO ] Released application lock Reader_6813a5af-078d-418e-8e24-8d62a0c838ad_Container_7LDNFPHPS43Y8Y8V 2020-07-09 01:23:21,424 [ERROR] System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated. at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
This issue is caused by data in [HardwareVaues].[Value], which is an nvarchar(max), being too large for the target fields in [ImportedContainerInstance], which are nvarchar(256). This issue can potentially affect 6 fields in the [ImportedContainerInstance] table, these fields are:
The fix was to update the SQL in Reader\ManageSoft\Container.xml to truncate the data coming from [HardwareVaues].[Value] to a length that was suitable for the [ImportedContainerInstance] fields as seen in the following code snippet (from ReadContainerInstances).
... LEFT(v_id.Value, 256) AS [ContainerID], LEFT(v_name.Value, 256) AS [Name], LEFT(v_imagetag.Value, 256) AS [ImageTag], CAST(v_cpu.Value AS float) AS [PartialNumberOfProcessors], CAST(v_mem.Value AS bigint) AS [TotalMemoryLimit], LEFT(v_entryp.Value, 256) AS [Entrypoint], LEFT(v_cmd.Value, 256) AS [Cmd], LEFT(v_status.Value, 256) AS [Status], ...
It was also identified that writers to the [ImportedContainerImage].[Type] could also be affected although the risk for this case was deemed very low. This case has also been addressed in this fix as seen in the following code snippet (from ReadContainerImages).
... rci.ExternalHostID, LEFT(v_type.Value, 32) AS [Type], v_repotags.Value AS [RepoTags], ...