DataHub Import failed on ETL 2, Populate_LookupComponent error.

DataHub import may fail on ETL 2 stage with error, see bellow.

Executing project ETL2_Stage_Transform package ETL2_Stage_Master.dtsx - DFT - Populate_LookupComponent:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Unspecified error".-- DFT - Populate_LookupComponent:Error: There was an error with OLE_DST - LookupComponent.Inputs[OLE_SRC - t_Stage_Output_OLE_DST - LookupComponent].Columns[Ca
rdType] on OLE_DST - LookupComponent.Inputs[OLE_SRC - t_Stage_Output_OLE_DST - LookupComponent]. The column status returned was: "The value violated the integrity constraints for the column.".

This error was identified as an problem with SNMP devices populating table model.t_DeviceComponent CardType column with some NULL values which is the reason why the import is failing, as DataHubs ETL 2 wont finish properly if NULL values are present.

To check, if this is the case with your import run the SQL queries bellow.

Run this query against the DataHub Stage DB:

SELECT CAST(CardType AS NVARCHAR(255)) AS CardType
FROM stage.t_DeviceComponent
GROUP BY CardType

Any NULL value will suggest that you are dealing with this issue. See output example:

Card Type
Module
Port
Sensor
Fan
Network Card
NULL
Chassis
Display Card
PowerSupply
Container
12
VNic
Disk
Other
Backplane
Sound Card

To fix the NULL value present in the model.t_DeviceComponent update this table with script bellow, this will change all NULL values to Unknown which is supported by the DataHub ETL 2 packages and import should go through as usual.

UPDATE model.t_DeviceComponent
SET CardType = 'Unknown'
WHERE CardType IS NULL