The resolution provided in this article will only be useful if the error is occurring on a Data Warehouse table in which a data selection rule is being applied.

Symptoms

When attempting to execute a table in your data warehouse, you receive an error message similar to the following:

The content of the error message described in this KB article is: Hresult: 0x80040E07  Description: "Conversion failed when converting the varchar value 'Test' to data type int.".

When a table in the data warehouse has multiple movements, it is typically not an issue to have a column that is unbalanced, only coming from some but not all of the source tables. However, if you create a data selection rule using a column like the one mentioned, you will receive the error above.

In the example below, the Posted Sales Transaction table is sourced from 8 tables. A data selection rule is being placed on the table to only bring in records that have an Item Ledger Entry Type = 1. Because the Item Ledger Entry Type is a column with Balanced data movements, this will work without error.

Below is an example of an unbalanced movement. the Sales Order Transactions table is sourced from both the Service Line and Sales Line tables in the Staging database. The "Company" field is sourced from both, but the "Move to Data Warehouse" column is not. Attempting to execute the table in this state will result in an error.

Resolution

If the field being used in the data selection rule already exists on the other source tables, you'll simply need to create data movements to the Data Warehouse column from the other source tables.

If the source fields do not exist, you will either:

A) Need to change the selection rule to use a column found on all tables

OR

B) Need to create a new column on the source tables missing the column and populate them appropriately.

Please note that any selection rule you are applying will be checking the values in the column. If the selection rule shows that only records which include "Yes" in the "Move to Data Warehouse" column, any records that do not have "Yes" will be discarded. Therefore, if all records are to be included from the source table that was missing the column, you would set that column to be a fixed value of "Yes"