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}")
 

Bắt đầu với hàm main Python được cung cấp và liên kết đầu ra SQL PersonnelItems của hàm đó, sau đó so sánh các lần chạy với 1,000 và 2,000 bản ghi được mô tả trong báo cáo. Kiểm tra xem các lỗi cột không hợp lệ thay đổi có phải do cách thư viện xử lý đầu ra SqlRow theo lô hay không; hoàn thành khi nhập được khoảng 4,000 phần tử nhân sự mà không có lỗi cột và giữ nguyên tất cả các hàng.