Let me walk through the complete implementation addressing all these points:
SFTP Integration Architecture:
Our Logic App has three main components:
-
File Monitor and Retrieval:
- Trigger: Recurrence (every 5 minutes)
- Action: SFTP-SSH ‘List files in folder’ for /inbound/leads
- Filter: Only process files with naming pattern ‘leads_[partnername]_[date].csv’
- For each file found, get file content and metadata
-
Data Transformation Pipeline:
- Parse CSV to JSON array
- Apply field mapping using Azure Table lookup
- Data quality checks (email format validation, required fields)
- Duplicate detection against existing Dynamics leads
-
Dataverse Import and Error Handling:
- Batch creation using Dataverse connector
- Error logging to Azure Storage Table
- File archival with processing status
Logic Apps Workflow Details:
The core workflow structure:
// Pseudocode - Main workflow steps:
1. Trigger: Recurrence every 5 minutes
2. List files in SFTP /inbound/leads folder
3. For each CSV file:
a. Get file content and metadata
b. Parse CSV to JSON array (Parse CSV action)
c. Look up field mapping from Azure Table
d. Transform data using Compose action
e. Check for duplicates in Dynamics
f. Create leads in batches of 100
g. Log results and move file to archive
// Error handling on each step with retry policy
Field Mapping Configuration:
We maintain a mapping table in Azure Table Storage with this structure:
- PartnerName (key)
- SourceField → TargetField mappings (JSON)
- DataTypeTransformations (JSON)
- DefaultValues (JSON)
The Compose action applies these mappings:
// Sample mapping transformation
{
"emailaddress1": "@{items('Apply_to_each')?['Email_Address']}",
"firstname": "@{items('Apply_to_each')?['First_Name']}",
"lastname": "@{items('Apply_to_each')?['Last_Name']}",
"mobilephone": "@{replace(items('Apply_to_each')?['Phone'], '-', '')}",
"leadsourcecode": "@{variables('PartnerLeadSourceCode')}"
}
Dataverse Connector Implementation:
Key aspects of our Dataverse integration:
-
Batch Processing:
- We process leads in batches of 100 using ‘Apply to each’ with concurrency control set to 1
- This prevents API throttling while maintaining reasonable throughput
- Each batch takes about 15-20 seconds to process
-
Duplicate Detection:
- Before creating each lead, we query Dynamics using the Dataverse ‘List rows’ action
- Query filter: emailaddress1 eq ‘{email}’ and createdon gt {last7days}
- If a match is found, we update the existing lead instead of creating new
- This leverages Dynamics’ native duplicate detection without custom code
-
Error Handling Strategy:
-
Each ‘Create row’ action has a parallel ‘Configure run after’ branch for failures
-
Failed records are logged to an Azure Storage Table with:
- Source file name
- Lead data (JSON)
- Error message from Dataverse
- Timestamp
- Retry count
-
A separate Logic App runs hourly to retry failed imports (up to 3 attempts)
-
After 3 failures, records are flagged for manual review and an email alert is sent
Data Quality and Validation:
Before sending data to Dynamics, we perform validation checks:
- Email format validation using regex
- Required fields check (firstname, lastname, companyname, email)
- Phone number normalization (remove dashes, spaces, parentheses)
- Date format conversion to ISO 8601
- Option set value mapping (converting partner’s status codes to Dynamics values)
Records failing validation are logged separately and not imported, preventing Dynamics validation errors.
Performance and Scalability:
Our current throughput:
- Average file size: 200-500 leads
- Processing time per file: 3-5 minutes
- Daily volume: 8-12 files, approximately 3,000 leads
- Success rate: 98.5% (1.5% require manual intervention due to data quality issues)
Monitoring and Alerting:
We implemented comprehensive monitoring:
-
Logic App run history tracks each execution
-
Azure Application Insights logs custom events for:
- Files processed
- Leads created/updated
- Errors encountered
- Processing duration
-
Email alerts for:
- Files sitting in SFTP for >30 minutes unprocessed
- Error rate exceeding 5%
- Dataverse API throttling events
Business Impact:
Since implementing this solution:
- Lead response time improved from 24+ hours to 15 minutes average
- Eliminated 2-3 hours of daily manual work
- Reduced data entry errors from ~5% to <0.5%
- Enabled real-time lead assignment to sales team
- Improved partner satisfaction with faster lead processing
Lessons Learned:
- Start with a pilot partner before rolling out to all partners
- Invest time in robust error handling - it’s worth it
- Use Azure Table Storage for configuration data that changes frequently
- Monitor API consumption carefully to avoid throttling
- Document the field mappings and share with partners to improve data quality at source
The total development time was about 3 weeks, including testing. The solution has been running in production for 6 months with minimal maintenance required. Happy to answer any specific technical questions about the implementation!