DataLoadingTool (2.7.1) in ArcgisPro 2.9.0 - The field is not nullable

[geopamplona]

Hello,

I have problems using DataLoadingTool(2.7.1) in ArcgisPro 2.9.0 with UtilityNetwork (schema version 2.71, model version 4).

It returns an error that it cannot append the source table.

2022-05-04 09:31:03.691 | WARNING | unmt_utils.Common.PropagateHandler:emit:7 - EXCEPTION
EXCEPTION
Traceback (most recent call last):
File "C:\arcgispro-py3-clone\lib\site-packages\dltsolutions\esri\gptools.py", line 116, in wrapper
return func(*args, **kwargs)
File "C:\arcgispro-py3-clone\lib\site-packages\dltsolutions\esri\gptools.py", line 465, in run
etl.main()
File "C:\arcgispro-py3-clone\lib\site-packages\dltsolutions\logic\etl.py", line 387, in main
self.load(self.transform(self.extract()))
File "C:\arcgispro-py3-clone\lib\site-packages\dltsolutions\logic\etl.py", line 360, in load
self._call_append(source=fc, target=self.target, field_mapping=fms)
File "C:\arcgispro-py3-clone\lib\site-packages\dltsolutions\logic\etl.py", line 332, in _call_append
raise e
File "C:\arcgispro-py3-clone\lib\site-packages\dltsolutions\logic\etl.py", line 313, in _call_append
arcpy.Append_management(**kwargs)
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 6706, in Append
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 6703, in Append
retval = convertArcObjectToPythonObject(gp.Append_management(*gp_fixargs((inputs, target, schema_type, field_mapping, subtype, expression), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteWarning: WARNING 000595: C:\Users\USER\AppData\Local\Temp\fc_95d486edcad1469d8bcde14bb242c36b0.fid contains the full list of records not able to be processed.
WARNING 000594: Input feature 1: The field is not nullable..
WARNING 000594: Input feature 2: The field is not nullable..
WARNING 000594: Input feature 3: The field is not nullable..
WARNING 000594: Input feature 4: The field is not nullable..
WARNING 000594: Input feature 5: The field is not nullable..
WARNING 000594: Input feature 6: The field is not nullable..
WARNING 000594: Input feature 7: The field is not nullable..
WARNING 000594: Input feature 8: The field is not nullable..
WARNING 000594: Input feature 9: The field is not nullable..
WARNING 000594: Input feature 10: The field is not nullable..

2022-05-04 09:31:05.285 | ERROR | __main__:main:29 - An error has been caught in function 'main', process 'MainProcess' (8032), thread 'MainThread' (10028):

Source feature class fields:

Target feature class fields:

it seems that the problem is in the assetgroup field, it has no null evaluators.

[geopamplona]

I have found that DLT makes a temporary copy of source feature class, what is the point of this, when is it used?

[TedHoward2]

That is admittedly a strange stack trace. Here's little background so we're on the same page. The "Execute Data Load" and "Append with Transformation" tools both call the gp Append tool to load data. The internal workflow is roughly:

1. Create a Table/Feature Class in memory
2. Add all Source fields using in loading + temporary fields for translation
3. Load Source into memory table
4. Calculate all temporary fields
5. Load memory data into Target

This workflow is required to do custom translations "in flight" to the Target. The exception here is that if the mapping is just a straight field map, ie. map Field1 to FieldA and Field2 to FieldB, then Append is called directly from Source to Target.

Now, I believe the issue has to do with Append attempting to load a null value into a Target field that is not nullable. However, I am unable to reproduce your specific error by forcing the tool to load a Source field which contains NULLs into a Target field that is not nullable. It may help to know which tool you are executing and what your mapping looks like for the source/target it is failing on. It does not have to be the assetgroup field, it could be any Target field that is not nullable.

[geopamplona]

Hi @TedHoward2 

Thanks for help, part of what you say I have inferred by reviewing the code of the etl.py file, line 314. I tried to export the feature class temp that creates a field "assetgroup : TEMP" and I managed to reproduce the isolated error.

However, to be clearer, I attach a minimal dataset that reproduces this error.

Under the working formula set by UtilityNetwork, I try to take the data from SewerDevice_XXX_XXX (from the gdb dataset_sewer_v1.gdb) to SewerDevice (from the gdb sewer_assetpackage.gdb). I attach result of the creation of workspace with all the detail, when you try to launch it, it breaks.

I run in ArcGIS 2.9.2; I applied the patches recently in case I could solve something.

Thank you very much!

[TedHoward2]

Thanks for your patience. The issue appears to be that the Feature Dataset in the Target workspace is in a different spatial reference than the Target feature classes. I recommend running Change Asset Package Spatial Reference (from untools) to convert everything to the correct SR in your Target. We will update DLT to catch this in a future release.

[geopamplona]

I have tested the workflow and it is valid.

Thank you very much !

[geopamplona]

Hello @TedHoward2 

My colleagues have implemented workflow you mentioned, however they have noticed that it is failing and the data is not migrating.

I have checked it again and indeed, the command finishes correctly but the data is not loaded in the AssetPackage.

The flow I have followed is as follows: I do Change Spatial Reference and then Execute Data Load.

All values in all fields are null.

Have you tried the same flow and were you able to migrate the data?

Thank you!

[TedHoward2]

When I run Execute Data Load using your sample data (after running Change Spatial Reference), the Asset Group field gets populated correctly. A number of other fields have values specifically because they have default values assigned. The rest of the fields are null because nothing else is mapped in the Mapping workbook which is expected.

[geopamplona]

Hi,

I have noticed that Create Data Load Workspace tool does not join fields that have exactly same name in my case.

Previously, in previous versions of DLT, this behavior occurred according to documentation, whereby fields that have same name are migrated.

In this case, fields have same name and tool is not recognizing them automatically.

I am creating the Data Loading Workspace in this way, with default arguments (to maintain compatibility over previous versions):

If I enable Field Name Similarity, it configures fields to migrate, but with errors, there are not only exact matches, for example Diameter and Diametro, it joins them, but it is not correct; in rest of the cases it is the result I expect.

As a conclusion, DLT has a different behavior between the last two versions. In the case of Arcgis PRO 2.9 I am a captive customer of the latest version of DLT, which does not work as expected.

[TedHoward2]

If you are still seeing problems with string matching your Source fields to Target fields, please create a new post as this is unrelated to the current thread.