Seems to be a limit on updated rows #278
Description
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"
}
]
}Source
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}")