Workaround for RTC 4.0.5 Data Manager ETLs when run against a 4.0.3 CCM App server

I found this bug today and wanted to share my solution to the problem. My scenario is I have Insight v1.1.1.3 deployed and integrated with 2 CLM deployments, one of which is version 4.0.5 and the other is version 4.0.3. The RTC Data Manager ETLs run fine against the 4.0.5 CCM server however, when running the 4.0.3 DM ETLs against the 4.0.3 CCM server, errors are thrown.

Viewing the ri_jdbc.log file, I was able to detect the first error in the “RTC_WorkItemApprovalState4.0.3” fact build (see below for full error). It is complaining about a column it is trying to query that does not exist. The column is ‘projectAreaArchived’.

When I checked the XML data configuration file in the XDC tool, the respective resource (WorkItemApprovalState) was indeed missing this data element.


The solution to this is to expose the required data element which indeed does exist in the RTC reportable REST API.

  1. Launch the XML Data Configuration tool
  2. Load the workitem403.xdc file
  3. Navigate to the data mapping table: “Resources” > “RTCWorkItem” > “WORKITEM_CCM” > “WorkItemApprovalState” > “WorkItemApprovalState
  4. Double-click on the data mapping table
  5. Select the “Columns” tab and then select “Modify”
  6. Navigate through the RTC reportable REST API to find the ‘/archived’ data element. Highlight it and select “Create”. This will add the new element to the data mapping template.
  7. Rename it to ‘projectAreaArchived’ by clicking in the “Table Column Name” cell and modifying the text
  8. Save the configuration
  9. Close the “WorkItemApprovaState” data mapping template tab
  10. You should receive a dialog that tells you the template has been changed and asks you to update it. Select “Ok”
  11. Save your configuration once again
  12. Your new column will appear faded. You have added the column but now need to load it. Highlight your new column and click on “Load”.
  13. Save your configuration again and this new column is now available made to Insight, hence the ETL will no longer complain about it.

There are 4 different places where this error will be thrown. You will only see one error at a time as the ETL halts upon the first error in this case. Here are the 4 fact builds this affects:


Follow the steps I provided above for each of these 4 fact builds and you can proceed with your deployment without error.

For reference, here is the full error found in the ri_jdbc.log:

query => SELECT “id”,
FROM “WORKITEM_CCM”.”WorkItemApprovalState”
WHERE modifiedsince=’1899-12-31 00:00:00′ AND projectAreaArchived=false
03/06/2014 13:27:27,444 ERROR Thread-5 : CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
03/06/2014 13:27:27,444 ERROR Thread-5 : CRRRE1203E: The column ‘projectAreaArchived’ does not exist. CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
Caused by: CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
… 1 more
03/06/2014 13:27:27,459 INFO main : CRRRE1228I: Close this statement

Leave a Reply

Your email address will not be published. Required fields are marked *