REINSP_DATE & REINSP_STATUS - Nullable Mismatch / No Source

[JuanitaMiles]

REINSP_DATE and REINSP_STATUS are two fields included in most inspection tables within the MDE schema. Within the MDE schema these fields are NOT nullable. However, no source is provided within the ETL.

Without any source data to populate these fields, all Inspection related tables will result in an "Append_Failure" table export which is not consistent with the MDE Schema.

MDE Tables that will likely be impacted by this issue include: AltBMPPolyInspections Source Tables: INSP_ANN_PRAC INSP_IMPV_REM TREE_INSP_REPORT AltBMPLineInspections Source Tables: INSP_OUTFALL_STAB INSP_SHORELINE INSP_STREAM AltBMPPointInspections Source Tables: INSP_SEPTIC BMPInspections Source Tables: INSP_SW SW_INSP_REPORT RESTBMPInspections Source Tables: INSP_SW

Please advise on a solution to this issue, and pass revised ETL on to Gerry/Brendan.

[dhenry-KCI]

@JuanitaMiles - these fields are optional in the new MDE schema released on May 2017. The County does not plan on providing data to MDE for these fields. They can remain Null.

[JuanitaMiles]

@gerrykelly - Gerry could you confirm whether or not the REINSP_DATE AND REINSP_STATUS fields within AltBMPPolyInspections, AltBMPLineInspections, AltBMPPointInspections, BMPInspections, and RESTBMPInspections, in the MDE Export are set to allow Null values.

I'm thinking this is the reason for the hang up of all the inspection tables. Based on Dustin's comment above, if they are not set to allow Null values, we will need to make this update.

I apologize if this is a Brendan question, please forward to him if necessary.

[leeensminger]

@JuanitaMiles I think the template MDE gdb for Gerry is in the project folder - take a look in there and you can also verify if these are nullable

[JuanitaMiles]

@leeensminger @gerrykelly I took a look at the master MDE schema located here: M:\2016\181603168.02\Reports\MDE_ETL\MASTER MDE Schema

All REINSP_ fields do not allow Null values. We will need to make this change to the Master MDE Schema.

[gerrykelly]

@JuanitaMiles @leeensminger The template FGDBs used by the ETL are in \vm-wsscmta\c$\Projects\BaltCoNPDES\ETL\Templates MDE_empty_withDomains.gdb is used when the ETL is using the revised schema MDE_empty_withDomains_unaltered.gdb is used if not using revised schema (since no changes have been made since testing began, those 2 DBs are exactly the same now) Because you believe this to be a valid change to the native MDE schema, those fields should be updated in both template FGDBs.

[leeensminger]

@JuanitaMiles Can you update the databases referenced above and set these fields to null.... once complete, this issue can be closed.