[1777042] Error walking route

Hi,

I am getting the error "Datatype (Decimal) is not supported!" when trying to walk from one table to another in a target system but can't see what I'm doing wrong. The schema definitely includes both tables, and the target system MSSQL) has an integer field storing the FK to an autonumber UID column in another table, and referential integrity is in force.

So Table 1 has an attribute table2_id where Table2.table2_id is the PK, and I want the description field from table 2.

When I test it, I get this exception:

[1777160] Property (vrtObj_EmployeeType@dbo.employee_master) could not read the value from system object (1000)!
    at VI.Projector.UI.ComponentModel.SystemObjectPropertyDescriptor.GetValue(Object component)
    at VI.Projector.Schema.SchemaProperty.GetValue(ISystemObject systemObject, SchemaPropertyGetValueOption option)
[1777042] Error walking route (employee_type_id.employee_type_code)!
    at VI.Projector.Schema.SchemaProperty.GetValue(ISystemObject systemObject, SchemaPropertyGetValueOption option)
    at VI.Projector.Schema.SchemaWalker.get_Item(String route, Object defaultValue)
Datatype (Decimal) is not supported!
    at VI.Projector.Schema.SchemaWalker.get_Item(String route, Object defaultValue)
    at VI.Projector.Schema.SchemaWalker.OnWalk(String[] route)

I looked at using other methods, but couldn't find one that would give me the description.

OK, I think I've got it sussed - you have to convert FKs to string for the path to work properly. However, it doesn't work consistently. For some table relationships I seem to need to convert the ID to string, for others I don't, and for a third one I'm looking at, there are no errors when walking to the object but the description field is not being returned.

OK, it all tests OK until I run the sync simulation. If an object has a NULL value in the ID column, an exception is thrown. Inside the target system I can see the problem objects with this query:

select * from employee_master
where ISNULL(country_id,0)=0  OR ISNULL(region_id,0)=0

In the sync, the error that is thrown looks like this:

[1777239] The mapping rule (Spare field no. 08 <-> vrtObj_CountryID_Code) was unable to execute synchronization between system objects (1750) and (1750) successfully!
    at System.Threading.Tasks.Task.<>c__DisplayClass11.<ExecuteSelfReplicating>b__10(Object param0)
    at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
    at System.Threading.Tasks.Parallel.<>c__DisplayClassf`1.<ForWorker>b__c()
    at VI.Projector.Mapping.Extensions.<>c__DisplayClass4_0.<MapObjects>b__0(SystemObjectMatchingPair pair)
    at VI.Projector.Mapping.SystemMappingRule.ApplyMapping(ISystemObject leftObject, ISystemObject rightObject, SystemMappingOptions options)
[1777160] Property (vrtObj_CountryID_Code@dbo.employee_master) could not read the value from system object (1750)!
    at VI.Projector.Mapping.SystemMappingRule.ApplyMapping(ISystemObject leftObject, ISystemObject rightObject, SystemMappingOptions options)
    at VI.Projector.Mapping.Rules.SinglePropertyComparisonRule.OnApplyMapping(ISystemObject leftObject, ISystemObject rightObject, SystemMappingOptions options)
    at VI.Projector.Mapping.Rules.SinglePropertyComparisonRule._GetValue(ISystemObject systemObject, ISchemaProperty property, IProjectorProcessContext context, Boolean useOldValues, Boolean handleAsSingleValue)
    at VI.Projector.Mapping.Rules.SinglePropertyComparisonRule.<>c__DisplayClass14_0.<_GetValue>b__0()
    at VI.Projector.Schema.SchemaProperty.GetValue(ISystemObject systemObject, SchemaPropertyGetValueOption option)
[1777042] Error walking route (VRT_country_idAsString.code)!
    at VI.Projector.Schema.SchemaProperty.GetValue(ISystemObject systemObject, SchemaPropertyGetValueOption option)
    at VI.Projector.Schema.SchemaWalker.get_Item(String route, Object defaultValue)
[1777027] Error loading a system object using the key property "country_id@dbo.countries"!
    at VI.Projector.Schema.SchemaWalker.get_Item(String route, Object defaultValue)
    at VI.Projector.Schema.SchemaWalker.OnWalk(String[] route)
    at VI.Projector.Schema.SchemaWalker._QueryData(ISystemConnection connection, ISchemaProperty keyProperty, Object keyValue, ISchemaProperty valueProperty)
    at VI.Projector.Connection.SystemConnection.QueryObject(ISchemaProperty keyProperty, Object value, QueryByKeyOptions query)
Specified cast is not valid.
    at VI.Projector.Connection.SystemConnection.QueryObject(ISchemaProperty keyProperty, Object value, QueryByKeyOptions query)
    at VI.Projector.Connection.Connectors.DistributionConnector.QueryObject(QueryObjectByKeyRequest request)
    at VI.Projector.Connection.SystemConnector.QueryObject(QueryObjectByKeyRequest request)
    at VI.Projector.Database.Native.NativeDatabaseConnector.OnQueryObject(ISchemaProperty keyProperty, Object value, QueryObjectByKeyRequest request)
    at VI.Projector.Database.Native.Access.SqlBasedImplementedSqlCodeFormatterBase.OnFormatComparison(NativeDatabaseSchemaProperty property, Object value, FilterCompareOperator compareOperator)
    at VI.Projector.Database.Native.Access.SqlBasedImplementedSqlCodeFormatterBase.OnFormatValue(NativeDatabaseSchemaProperty property, Object value, FormatValueOption option)

Any advice on how to get the object reference to just skip if the value is NULL? I already have conditions in place so the mapping is only set if the ID has a numerical value.