Seems to be a limit on updated rows

Check for a solution in the Azure portal

Done. Nothing found.

Investigative information

• Timestamp: 2023-04-21 14:19:47.401
• Function App version: 4.17.3.3
• Function App name: cardAccess
• Function name(s) (as appropriate): PersonnelFileTrigger
• Invocation ID: 8a20b815-a601-4ab0-b61d-025147fef78b
• Region: EastUS

Repro steps

Provide the steps required to reproduce the problem:

Import an XMl file with more than 1,000 elements

Expected behavior

Import an XML file of ~4,000 personnel elements
All should appear in the target SQL server table

Actual behavior

Import an XML file of ~4,000 personnel elements

When I import only 1,000 records, it works fine. In the code:
start_recno = 0
end_recno = 1000

When I import only the 2nd thousand records, it works fine. In the code:
start_recno = 1000
end_recno = 2000

When I import 2,000 records I get a meaningless error message. Code:
start_recno = 0
end_recno = 2000

2023-04-21 14:19:53.808
Invalid column name 'Text4'. Invalid column name 'Text4'.
Error

Note: The target table indeed does have such a column, and it was populated by smaller runs. The reported Invalid column varies from run to run. Sometimes Text4, sometimes Text6 and Text7, sometimes MiddleName. Seemingly random, but all valid columns.

Known workarounds

none

Related information

Provide any related information

• Programming language used: Python
• Links to source: https://portal.azure.com/#view/WebsitesExtension/FunctionMenuBlade/~/code/resourceId/%2Fsubscriptions%2F8a237898-8564-4180-9423-9c9befb66299%2FresourceGroups%2Fcn100898%2Fproviders%2FMicrosoft.Web%2Fsites%2FcardAccess%2Ffunctions%2FPersonnelFileTrigger
• Bindings used

{
  "bindings": [
    {
      "name": "myblob",
      "path": "ccure/Personnel/{name}",
      "connection": "recurringintegrations_STORAGE",
      "direction": "in",
      "type": "blobTrigger"
    },
    {
      "name": "PersonnelItems",
      "type": "sql",
      "direction": "out",
      "commandText": "dbo.Personnel",
      "connectionStringSetting": "live_100898"
    },
    {
      "name": "CredentialItems",
      "type": "sql",
      "direction": "out",
      "commandText": "dbo.Credential",
      "connectionStringSetting": "live_100898"
    }
  ]
}

import logging
import azure.functions as func
import xml.etree.ElementTree as ET
from datetime import datetime

def main(myblob: func.InputStream, PersonnelItems: func.Out[func.SqlRow], CredentialItems: func.Out[func.SqlRow]):

    logging.info(f"Python blob trigger function processed blob \n"
                 f"Name: {myblob.name}\n"
                 f"Blob uri: {myblob.uri} \n")

    XMLContent = myblob.read()
    # create element tree object from string and start at root
    root = ET.fromstring(XMLContent)
 
    # iterate items
    start_recno = 0
    end_recno = 1000
    recno = 0
    debug = True
    rows = []
    for item in root.findall('./SoftwareHouse.NextGen.Common.SecurityObjects.Personnel'):
        recno = recno + 1
        if recno < start_recno:
            continue
        if recno >= end_recno:
            break
        now_datetime = datetime.now().replace(microsecond=0).isoformat()
        row_d = {"ImportDate": now_datetime, "FileName": myblob.name, "RecordNumber": recno }
        for child in item:
            if child.tag == 'SoftwareHouse.NextGen.Common.SecurityObjects.Credential':       # skip detail for now
                continue
            if "LastModifiedTime" in child.tag:
                orig_date = child.text
                date_obj = datetime.strptime(orig_date[:-9].strip(), "%m/%d/%Y %I:%M:%S %p")
                iso_date = date_obj.isoformat()
                row_d[child.tag] = iso_date
            else:       # "normal" elements
                row_d[child.tag.strip()] = child.text.strip()
        if debug:
            logging.info(f"{row_d}")
        # store in a list
        rows.append(func.SqlRow(row_d))
    # write the list of rows to the database
    PersonnelItems.set(rows)
    logging.info(f"row count: {recno}")
 

Start with the supplied Python main function and its PersonnelItems SQL output binding, then compare the 1,000- and 2,000-record runs described in the report. Check whether the changing invalid-column errors are caused by the library's handling of batched SqlRow output; done means importing about 4,000 personnel elements without column errors and preserving all rows.