关于使用Azure可扩展服务解析非标准格式EDI文件并持久化至SQL数据库的解决方案咨询

Hey there, let's walk through the best Azure services to tackle your non-standard EDI parsing challenge while keeping things scalable—no more maintaining clunky, one-off parsing libraries! Here's a breakdown of options tailored to your file types and persistence needs:

1. Azure Data Factory (ADF) #

This is your go-to for fully managed ETL/ELT workflows with built-in scalability. It cuts down on custom code while handling most of your file types:

• For *.CSV, *.TXT, *.XML: Even non-standard variants work here. Use ADF's custom column mapping, derived column transformations, or regex matching to extract exactly what you need. For wonky TXT files, you can define fixed-width schemas or delimiter rules, then tweak with expressions to clean up data.
• For *.DAT: If it's a custom binary or text format, use ADF's Custom Activity to run your existing parsing logic (packaged as a Docker image or Azure Function), or leverage Mapping Data Flows with custom expressions to parse the structure.
• SQL Persistence: ADF has native connectors for Azure SQL Database/SQL Server—just map your parsed data to database tables and let it handle bulk loads efficiently.
• Scalability: ADF auto-scales based on your workload, so it can handle anything from hundreds to millions of files without you managing servers.

2. Azure Functions + Azure Cognitive Services (for Image Files) #

Perfect for event-driven, custom parsing—especially critical for your image files:

• Image Files: Pair Azure Functions with the Computer Vision API (OCR feature) to pull text from images. Then write lightweight code in the Function to structure that text (using regex or string manipulation) into usable data.
• Other Formats: Wrap your existing parsing libraries into Azure Functions, then set up a Blob Storage Trigger to automatically run the parser whenever a new .DAT or non-standard .TXT hits your storage.
• SQL Persistence: Use the Microsoft.Data.SqlClient library directly in your Function to write parsed data to SQL, or pass the structured data to ADF for bulk processing.
• Scalability: It's serverless, so Azure automatically spins up more instances during peak loads and scales down when idle—cost-effective and low-maintenance.

3. Azure Integration Accounts (EDI-Focused Workflows) #

If you want an EDI-optimized solution that can handle non-standard formats:

• While it's built for standard EDI (X12, EDIFACT), you can define custom protocols or use Azure's mapping tools to translate non-standard .DAT/.TXT files into structured JSON/XML.
• For images, integrate Computer Vision's OCR directly into the integration account workflow to extract text before mapping.
• SQL Persistence: Tie it to Azure Logic Apps (which integrates deeply with Integration Accounts) and use the SQL Server connector to write data to your database.
• Scalability: Designed for enterprise-level EDI volumes, it supports batch processing and auto-scales with Logic Apps to handle high traffic.

4. Azure Logic Apps (Low-Code Workflows) #

Great if you want to build parsing pipelines quickly without heavy coding:

• For CSV/XML/TXT, use built-in connectors to parse even non-standard versions, then use Logic Apps' text processing actions (like regex matching) to clean up data.
• For images, use the pre-built Computer Vision connector to run OCR, then structure the results with built-in tools.
• For .DAT files, call an Azure Function or custom API to handle the custom parsing logic.
• SQL Persistence: Use the native SQL connector to insert or update data directly in your database—supports batch operations too.
• Scalability: Logic Apps auto-scales based on workflow load, so it's perfect for scheduled or event-triggered parsing tasks.

Recommended Hybrid Setup #

For your mix of file types, a combined approach works best for scalability and maintainability:

1. Store all EDI files in Azure Blob Storage as a single entry point.
2. Use ADF Mapping Data Flows for CSV/XML/TXT—parse and write directly to SQL.
3. Use Azure Functions with Blob triggers for .DAT files (reuse your existing parsing logic) and image files (with Computer Vision OCR).
4. Write parsed data from Functions directly to SQL, or pass it to ADF for bulk processing.
5. Monitor everything with Azure Monitor to track pipeline health and parsing success rates.

This setup is fully extensible—when you add new file types later, just drop in a new Function or ADF transformation without overhauling your entire system.

内容的提问来源于stack exchange，提问作者Ram