search

kcigeospatial / balt_co_ETL

This will provide a place to track internal issues around the Baltimore County NPDES NCT application ETL.
0 stars 0 forks source link

ETL - MDE.AltBMPLine.BMP_DRAIN_AREA is non-nullable but should be nullable, and the ETL has no source assigned so successful append is impossible #82

Open dhenry-KCI opened 6 years ago

dhenry-KCI commented 6 years ago

The ETL v8 excel file shows that field MDE.AltBMPLine.BMP_DRAIN_AREA has no source. It does not seem that the ETL populates this field.

That should be OK, because the MDE MS4 Geodatabase Design and User Guide and the MDE schema excel file both state that field MDE.AltBMPLine.BMP_DRAIN_AREA is optional.

However, the template MDE_NPDES_MS4_052017.mdb geodatabase has AltBMPLine.BMP_DRAIN_AREA as non-nullable. And the ETL tool uses an MDE geodatabase template that also has AltBMPLine.BMP_DRAIN_AREA as non-nullable (see screen capture below).

Thus, when the ETL tool runs, it is incapable of writing the required MDE.AltBMPLine feature class.

I see two ways to resolve this:

  1. Revise the MDE MS4 geodatabase template used by the ETL tool to make AltBMPLine.BMP_DRAIN_AREA nullable.

  2. Revise the ETL script to write -99 into AltBMPLine.BMP_DRAIN_AREA.

Screen cap: image

However, the MDE schema for AltBMPLine indicates that BMP_DRAIN_AREA is non-nullable. Therefore, the ETL as written cannot successfully write any records out to the MDE required AltBMPLine feature class.

ORIGINAL ISSUE HERE: https://github.com/kcigeospatial/balt_co_npdes/issues/394

dhenry-KCI commented 6 years ago

@gerrykelly The ETL is failing to append records due to the required field AltBMPLine.BMP_DRAIN_AREA being non-nullable. This is currently a schema discrepancy issue between MDE's guidance documents and the database. Please update the ETL to populate the AltBMPLine.BMP_Drain_Area with -99 for all records

gerrykelly commented 6 years ago

@dhenry-KCI Done and working at KCI, but the append still fails (Append_Failure_ALT_LINES_to_AltBMPLine is created). So there are other cause(s) to be determined.

gerrykelly commented 6 years ago

@dhenry-KCI Still fails because ALT_LINES.SITE_NAME is used to populate PROJECT_NAME for AltBMPLine, but ALT_LINES.SITE_NAME has a length of 75 while the target has a length of 25 and there are values longer than 25

dhenry-KCI commented 6 years ago

@gerrykelly trim to first 25 characters of Projectname

gerrykelly commented 6 years ago

@dhenry-KCI Now succeeds at KCI

dhenry-KCI commented 6 years ago

Confirmed at KCI. Ready for County testing.