While upgrading to a newer release and on Clarity 15.9.3 and SQL Server database, the Load Data Warehouse job fails while having a Time-Varying attribute enabled for DWH in the Roadmap Item object, throwing the following error:
CREATE_CONSTRAINTS_TSV - org.pentaho.di.core.exception.KettleStepException:
Error while running this step!
Couldn't execute SQL: ALTER TABLE DWH_RDM_ROADMAP_IT00_FACTS
ADD CONSTRAINT DWH_RDM_ROADMAP_IT00_FACTS$2
FOREIGN KEY (ROADMAP_ITEM_KEY)
REFERENCES DWH_RDM_ROADMAP (ROADMAP_KEY)
ON DELETE CASCADE
[CA Clarity][SQLServer JDBC Driver][SQLServer]Column 'DWH_RDM_ROADMAP.ROADMAP_KEY' is not the same data type as referencing column 'DWH_RDM_ROADMAP_IT00_FACTS.ROADMAP_ITEM_KEY' in foreign key 'DWH_RDM_ROADMAP_IT00_FACTS$2'.
at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
Couldn't execute SQL: ALTER TABLE DWH_RDM_ROADMAP_IT00_FACTS
ADD CONSTRAINT DWH_RDM_ROADMAP_IT00_FACTS$2
FOREIGN KEY (ROADMAP_ITEM_KEY)
REFERENCES DWH_RDM_ROADMAP (ROADMAP_KEY)
ON DELETE CASCADE
[CA Clarity]
Release : 15.9.3
Database: MS SQL Server
This issue is not reproducible in higher Clarity releases, such as 16.1.2.
Temporarily exclude the attribute and run the Full Load Data Warehouse and once it succeeds, continue with the upgrade. Include the attribute back once on 16.1.2.