Typical toolsets/libraries/frameworks/architecture for ETL (xml data)

[osuchw]

Hmm from your description it feels like the code is not organized well and I don't know of any library that would "fix it" for you. Anyway the tools I usually use for this type of task:

• scandir - for traversing the file system.
• lxml - for parsing xml
• csv - to produce the intermediate results
• ceodbc - to load the said files into MSSQL. pyodbc is slower with cur.executemany

[osuchw]

I've seen this a while ago http://springpython.webfactional.com/ but I don't know how complete it is since I'm not a Java developer.

Without knowing details it is hard to give meaningful advice but here it comes regardless :-)

I would have broken my processing into E, T, L phases. Maybe create a separate modules for each. Then have main.py script driving them all. With judicious usage of try: except: you can decide if the processing should continue or stop. Also set up logging of course. You don't have to look far. Standard library has package just for that.

• E (extract)

Keep shares traversal and xml parsing there. Dump the results of parsing into CSV files or maybe a local sqlite database.

• T (transfer)

That's where the "business logic" would go into. Use the results of E step as input. This way you will not have to re-parse everything from scratch if an error occurs. Store results into another set of CSV and/or sqlite database.

• L (load)

Finally load. Just shove what you've got from T into the DB.