I am trying to read in the SchemaMapper from a Microsoft SQL Server Non-Spatial table based on a where clause. The value in the where clause depends on the value from another table. So I first read that other table and create an attribute with that value. In the SchemaMapper the configuration is as follows:

You can already notice that there are no options to assign an attribute value or open a text editor for the WHERE Clause field.

When I run the workspace I get the following error:

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `SELECT * FROM DataOwner.[RAC_SCHEMA_DEFINITION] WHERE MAPPING_ID = '@Value(MAPPING_ID)''. Provider error `(-2147217900) Conversion failed when converting from a character string to uniqueidentifier.'

 

A fatal error has occurred. Check the logfile above for details

 

SchemaMapper_Mapper(SchemaMappingFactory): Could not read feature: A fatal error has occurred. Check the logfile above for details

I appears that it is not possible to assign an attribute value inside the where clause. It just inserts the @Value(...) as text. When I try this inside a FeatureReader for Microsoft SQL Server Non-Spatial it is possible to assign an attribute value or open a text editor and running it works like a charm.

Is this a bug or non-existent functionality? Or is there a reason that this is not possible?

I am already thinking of a workaround.

 

Option is using a parameter.

 

Populating the parameter with an @Value(...) will give the same result as the above issue.

 

It appears to work with a pre-defined string as input parameter and using $() inside the SchemaMapper where clause. Problem is that the value is than not dynamic anymore.

 

I could look into catching the wanted value with Python inside the parameter, but I would like to minimize Python/arcpy.

Other option is adding (a) row(s) to the input data for the SchemaMapper and using a SchemaMapper filter instead of a where clause. This could work, but input data can have an amount of rows order of millions of rows, so I would like to avoid adding an attribute to that data. Also the mapping table for the SchemaMapper could have many rows that I won't need and I would like to avoid processing these.

Any other options? Or explanation to my earlier question?