Integration

KingswaySoft: Enterprise data integration for Dynamics 365

April 19, 2025
Sabhat Qureshi
12 min read

What is KingswaySoft?

KingswaySoft is a widely used SQL Server Integration Services (SSIS) integration toolkit designed specifically for Microsoft Dynamics 365, Dataverse, and related Microsoft cloud services. For many years it has been a common choice for enterprise-grade data integration, migration, and synchronization scenarios involving Dynamics 365.

Unlike generic ETL tools or low-code platforms like Power Automate, KingswaySoft provides deep, native integration with Dynamics 365 metadata, supports complex data transformation requirements, and handles enterprise-scale volumes with performance optimizations specifically tuned for CRM data operations.

Why KingswaySoft over alternatives?

  • Native Dynamics 365 support: Built-in understanding of CRM entities, relationships, option sets, and platform-specific behaviors
  • High-volume processing: Handles millions of records efficiently with parallel processing and batch optimization
  • Advanced error handling: Sophisticated error routing, retry logic, and detailed logging capabilities
  • Complex transformations: Full SSIS capabilities for data cleansing, validation, and transformation beyond simple field mapping
  • Enterprise features: Support for upsert operations, early bound entities, custom workflow activities, and advanced security
  • Proven reliability: Trusted by thousands of organizations for mission-critical data integration workloads

SSIS Integration Toolkit components

The KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics 365 includes a comprehensive set of components designed to handle every aspect of data integration.

Core data flow components

Dynamics 365 source component

Extracts data from Dynamics 365 using FetchXML or native queries. Supports incremental loads based on modified dates, parallel reads for performance, and automatic metadata detection.

Key features:

  • FetchXML query builder with visual editor
  • Incremental load support using modifiedon/createdon filters
  • Automatic paging for large result sets
  • Read deleted records from RecycleBin for audit scenarios
  • Support for both early-bound and late-bound entity types

Dynamics 365 destination component

Writes data to Dynamics 365 with support for Create, Update, Upsert, and Delete operations. Automatically handles parallel processing, batch sizing, and retry logic.

Key features:

  • Intelligent upsert operations using alternate keys or primary keys
  • Configurable batch sizes for optimal throughput
  • Parallel threading for maximum performance
  • Owner assignment and business unit management
  • Support for special columns (statecode, statuscode, ownerid)
  • Custom workflow execution post-operation

Advanced components

  • Lookup component: Performs reference data lookups against Dynamics 365 to resolve GUIDs from alternate keys
  • Attachment source/destination: Extracts and loads attachments, notes, and documents with base64 encoding handling
  • Execute multiple component: Batches multiple operations into a single request for reduced API overhead
  • Duplicate detection component: Leverages Dynamics 365's built-in duplicate detection rules during data loads
  • SharePoint integration: Direct integration with SharePoint Online for document management scenarios
  • Premium components: Additional components for Azure Blob Storage, Salesforce, ServiceNow, and other cloud platforms

Data migration scenarios

Initial data migration from legacy CRM

Migrating from legacy CRM systems to Dynamics 365 is one of the most common and critical use cases for KingswaySoft. A well-architected migration strategy minimizes downtime, preserves data integrity, and maintains business continuity.

Migration strategy best practices:

  • Phase 1 - reference data: Start with users, business units, teams, currencies, and option set mappings
  • Phase 2 - master data: Migrate accounts, contacts, and products with parent-child relationship handling
  • Phase 3 - transactional data: Import opportunities, quotes, orders, and invoices with lookups to master data
  • Phase 4 - activities & notes: Migrate emails, phone calls, tasks, appointments, and notes with proper regarding relationships
  • Phase 5 - attachments: Transfer document attachments, images, and files using annotation entities
  • Phase 6 - historical data: Archive or selectively import historical records based on retention policies

💡 Migration best practice:

Always perform migrations in a sandbox environment first and validate data quality before proceeding to production. Create comprehensive data mapping documents that define source-to-target field mappings, transformation rules, and business logic.

Ongoing data synchronization

Many organizations need to keep Dynamics 365 synchronized with external systems such as ERP platforms, e-commerce systems, or custom applications.

Synchronization patterns:

  • Delta synchronization: Track modifiedon timestamps to process only changed records since last run
  • Bidirectional sync: Implement conflict resolution strategies when both systems can update the same records
  • Real-time vs batch: Use SQL Server Agent jobs for scheduled batches or Service Bus triggers for near real-time sync
  • Change tracking: Leverage SQL Server change tracking or change data capture (CDC) for incremental loads from databases
  • Watermark pattern: Store last sync timestamp in configuration tables to resume processing after failures

Bulk data updates and cleansing

KingswaySoft is invaluable for performing bulk data cleanup operations that would be impractical through the UI or manual processes.

Common bulk operations:

  • Mass update of owner assignments based on territory or business rules
  • Deduplication of records using fuzzy matching and merge operations
  • Data standardization (address formatting, phone number cleanup, name capitalization)
  • Bulk state transitions (qualifying leads, closing opportunities, deactivating records)
  • Historical data archival to external storage with selective deletion from CRM
  • Correction of data quality issues identified through reports or audits

Upsert operations and alternate keys

One of KingswaySoft's most powerful features is its native support for upsert (update or insert) operations using alternate keys. This capability is essential for integration scenarios where you need to match records based on business keys rather than GUIDs.

Understanding upsert logic

Upsert operations eliminate the need for separate lookup steps to determine if a record exists. The component automatically performs the check and routes to either update or insert based on the result. alternate keys in Dataverse.

Upsert configuration options

  • By primary key (GUID): Use when source system provides Dynamics 365 GUIDs
  • By alternate key: Define custom alternate keys (e.g., account number, employee ID, external system ID)
  • By lookup field: Match on single field lookups like email address or name combinations
  • Composite keys: Use multiple fields together for uniqueness (e.g., first name + last name + company)

Handling upsert errors

Upsert operations can fail for various reasons: duplicate key conflicts, required field validation, plugin errors, or business rule violations.

  • Configure error output to redirect failed rows to staging tables for investigation
  • Log detailed error messages including entity ID, operation type, and error description
  • Implement retry logic for transient failures (API throttling, timeout exceptions)
  • Set up email notifications for critical upsert failures requiring immediate attention
  • Maintain audit trails of successful vs failed operations for reconciliation

Error handling and logging

Enterprise integrations must handle errors gracefully and provide comprehensive logging for troubleshooting. KingswaySoft provides multiple layers of error handling that work together to create resilient integration processes.

Error output configuration

Every KingswaySoft component includes an error output path that captures failed rows along with error descriptions.

Error routing strategies

  • Redirect row: Send failed rows to error table while continuing to process successful rows
  • Fail component: Stop entire package execution on first error - use for critical validations
  • Ignore failure: Continue processing and log errors without stopping - use for optional operations
  • Conditional routing: Use derived column transforms to categorize errors by type and route differently
  • Error staging: Write errors to dedicated staging tables with original data for manual correction and reprocessing

Comprehensive logging framework

Implement a logging framework that captures execution metrics, error details, and performance statistics.

Logging best practices:

  • Create integration log tables with columns for package name, start time, end time, rows processed, errors
  • Use SSIS event handlers to capture OnError, OnWarning, and OnInformation events
  • Store error details including error code, error description, affected entity, and failed record identifier
  • Implement row counting at each transformation step to identify where data loss occurs
  • Log pre-execution and post-execution record counts for reconciliation
  • Archive historical log data for trend analysis and compliance requirements

⚠️ Critical logging tip:

Never log sensitive data (passwords, API keys, credit card numbers) to error tables or log files. Use data masking for PII (personally identifiable information) in logs to comply with GDPR and other privacy regulations.

Retry logic and transient fault handling

Cloud services like Dynamics 365 occasionally return transient errors due to throttling, network issues, or service maintenance. Implement exponential backoff retry logic to automatically recover from these temporary failures.

  • Configure retry count and delay interval in destination component properties (typically 3-5 retries)
  • Use exponential backoff: 5s, 10s, 20s, 40s delay between retry attempts
  • Distinguish between retryable errors (throttling, timeout) and permanent errors (validation failures)
  • Implement circuit breaker pattern: stop processing if error rate exceeds threshold
  • Monitor API service protection limits to avoid throttling in the first place

Performance optimization

Performance optimization is critical for enterprise integration scenarios processing millions of records. KingswaySoft provides numerous tuning options to achieve maximum throughput while respecting Dynamics 365 service protection limits. API limits and service protection.

Parallel processing configuration

KingswaySoft supports multi-threaded parallel processing at both the package level and component level. Proper configuration of parallelism can reduce processing time by 10x or more.

Parallelism settings

  • Component-level threading: Destination components support 1-16 parallel threads (default: 4)
  • Batch size: Number of records per batch sent to Dynamics 365 (recommended: 50-200)
  • SSIS EngineThreads: Control data flow task parallel execution (default: 10)
  • MaxConcurrentExecutables: Package-level control for concurrent task execution (default: -1 for unlimited)

Optimal configuration: Start with 4-8 threads with batch size of 100. Monitor API throttling errors and adjust down if hitting service protection limits.

Batch size optimization

Batch size directly impacts throughput and API consumption. Too small and you waste API calls on overhead; too large and you risk timeout errors or memory issues.

Batch size guidelines:

  • Simple entities (no plugins): 200-500 records per batch for maximum throughput
  • Complex entities (with plugins/workflows): 50-100 records to avoid timeouts from business logic
  • Entities with attachments: 10-25 records due to payload size limitations
  • Bulk delete operations: 100-250 records depending on cascade delete relationships
  • Cross-entity updates: 50-100 records when updating multiple related entities in a transaction

Memory and buffer optimization

SSIS uses memory buffers to move data through the pipeline. Tuning buffer settings can significantly improve performance for large datasets.

  • DefaultBufferMaxRows: Set to 10,000-50,000 based on row size (default: 10,000)
  • DefaultBufferSize: Typically 10-100 MB (default: 10 MB)
  • MaximumMemory: Allocate sufficient RAM for the integration services service (recommend 8-16 GB minimum)
  • 64-bit execution: Always run packages in 64-bit mode for access to more memory
  • Asynchronous components: Minimize transformations that require blocking operations (sort, aggregate)

💡 Performance monitoring:

Use SSIS performance counters and SQL Server Profiler to identify bottlenecks. Monitor buffer spooling events, rows per second, and BLOB bytes read/written.

Network and connection optimization

Network latency between your SSIS server and Dynamics 365 data centers impacts performance. Deploy strategically to minimize round-trip time.

  • Host SSIS servers in Azure regions close to your Dynamics 365 instance
  • Use Azure virtual machines with Premium SSD for SSIS runtime for consistent I/O performance
  • Enable connection pooling to reuse authenticated connections
  • Use Azure ExpressRoute for dedicated network connectivity if processing highly sensitive data
  • Monitor network latency and packet loss between SSIS server and Dynamics 365 endpoints

KingswaySoft vs alternatives

KingswaySoft vs Power Automate (flow)

Power Automate is excellent for lightweight automation and integration scenarios, but has significant limitations for enterprise data integration workloads.

When to choose KingswaySoft over Power Automate:

  • Volume: Processing more than 10,000 records per day
  • Complexity: Complex data transformations, cleansing, or calculations requiring procedural logic
  • Performance: Need for parallel processing, batch optimization, or sub-hour processing windows
  • Error handling: Sophisticated error routing, retry logic, and transaction management
  • Scheduling: Flexible scheduling with dependencies, failure handling, and job chaining
  • Cost: High API call volumes make Power Automate expensive compared to SSIS infrastructure costs

When Power Automate is sufficient:

  • Simple field-level synchronization between systems (under 1,000 records/day)
  • Event-driven workflows triggered by CRM events or user actions
  • Lightweight approval workflows and notifications
  • Integration with Microsoft 365 services (SharePoint, Teams, Outlook)
  • No-code/low-code requirements where business users manage integrations

KingswaySoft vs Azure Data Factory

Azure Data Factory (ADF) is Microsoft's cloud-native ETL platform. While powerful for general-purpose data integration, it lacks Dynamics 365-specific features that KingswaySoft provides.

  • KingswaySoft advantages: Native understanding of CRM metadata, built-in upsert logic, option set handling, lookup components, and specialized error handling for CRM operations
  • ADF advantages: Serverless execution, broader connectivity to non-Microsoft systems, integration with Azure services, modern UI, and built-in Git integration
  • Hybrid approach: Some organizations use ADF for orchestration and call SSIS packages for Dynamics 365-specific operations

Licensing and deployment considerations

Licensing model

KingswaySoft licensing is server-based with perpetual licenses. Each production server requires a license, but development and test environments are included at no additional cost.

  • Integration toolkit: Core license for Dynamics 365 connectivity (most common purchase)
  • Premium components: Additional licenses for Azure, Salesforce, ServiceNow, and other connectors
  • SSIS Productivity Pack: Enhanced components for general SSIS development (optional)
  • Annual maintenance: Includes updates, support, and access to new features (typically 20% of license cost)
  • Disaster recovery: Standby servers for HA/DR scenarios are covered under primary license

Deployment architecture

Enterprise deployments require high availability, scalability, and security. Consider these architecture patterns for production environments.

  • Single server: Suitable for small deployments with limited integration requirements
  • Scale-out: Multiple SSIS servers with SQL Server Agent job distribution for parallel execution
  • SSIS catalog: Use SSISDB catalog for centralized package management, logging, and execution control
  • Azure VM deployment: Host SSIS on Azure VMs for proximity to Dynamics 365 and elastic scaling
  • Azure-SSIS Integration Runtime: Run SSIS packages in Azure Data Factory for serverless execution
  • Monitoring: Implement comprehensive monitoring with SQL Server Reporting Services dashboards

💡 Deployment best practice:

Use SSIS project deployment model with parameters and environment variables for configuration management. Store connection strings, API endpoints, and credentials in SSISDB environment variables.

Advanced scenarios and real-world use cases

Multi-system master data management

Organizations often need to synchronize customer master data across Dynamics 365, ERP systems (SAP, Oracle) and data warehouses. KingswaySoft supports both hub-and-spoke and peer-to-peer MDM architectures.

  • Implement golden-record logic to decide which system is the source of truth for each attribute
  • Handle conflicts when several systems update the same customer at the same time
  • Maintain cross-reference tables that map customer IDs across all integrated systems
  • Apply data quality rules to validate and cleanse data before it is distributed
  • Keep an audit trail of which system originated each change, and when

E-commerce integration

Integrate Dynamics 365 with e-commerce platforms such as Shopify, Magento (Adobe Commerce) or a custom web store to synchronize products, pricing, inventory, orders and customer data.

  • Product catalog synchronization, including price tiers and stock levels
  • Order creation in Dynamics 365 when customers order online
  • Two-way creation and updates of customer accounts
  • Stock updates from Dynamics 365 to prevent overselling
  • Order status updates from the fulfillment system back to the e-commerce platform

Data warehouse and analytics integration

Extract data from Dynamics 365 to load enterprise data warehouses or analytical databases for business intelligence and reporting. As an alternative, look at Azure Synapse Link for Dataverse.

  • Incremental ETL based on the modifiedon timestamp
  • Slowly changing dimension (SCD) Type 2 handling to keep history
  • Fact tables loaded from transactional CRM data (opportunities, cases, orders)
  • Dimension tables built from reference and master data tables
  • Power BI on top for current and historical reporting

Troubleshooting common issues

API service protection limit errors

Dynamics 365 enforces service protection API limits to ensure fair resource usage. Exceeding these limits results in 429 "Too Many Requests" errors. service protection API limits.

  • Symptoms: Package failures with "Number of requests exceeded the limit" error messages
  • Causes: Too many parallel threads, small batch sizes causing excessive API calls, or other concurrent integrations
  • Solutions: Reduce thread count to 4-6, increase batch size to 100-200, implement retry logic with exponential backoff

Authentication and connection issues

Authentication failures are common during initial setup or after certificate/password rotations.

  • OAuth token expiration: Ensure connection manager has valid refresh tokens or use service principal authentication
  • Service principal configuration: Register app in Microsoft Entra ID with appropriate API permissions
  • MFA challenges: Service accounts used for authentication should have MFA disabled or use certificate-based authentication
  • Connection string format: Verify organization URL, authentication type, and credentials are correctly formatted
  • Firewall/proxy issues: Ensure SSIS server can reach *.dynamics.com endpoints on port 443

Performance degradation over time

Packages that initially perform well may slow down as data volumes grow.

  • Add indexes to staging tables used for lookups and joins
  • Implement incremental loads instead of full refreshes when possible
  • Archive historical data that no longer needs to be processed
  • Partition large tables to improve query performance
  • Monitor SSIS server resources (CPU, memory, disk I/O) and scale vertically if needed
  • Review execution plans for database queries to identify missing indexes

Conclusion

KingswaySoft SSIS Integration Toolkit remains a strong choice for enterprise-grade Dynamics 365 data integration. Its comprehensive feature set, proven reliability, and performance optimization capabilities make it valuable for organizations managing complex integration requirements.

The key to success with KingswaySoft is understanding the platform's capabilities, implementing robust error handling and logging, optimizing for performance with proper parallelism and batch sizing, and following deployment best practices.

While newer cloud-native alternatives like Azure Data Factory and Power Automate have their place in the integration landscape, KingswaySoft's deep Dynamics 365 integration capabilities, enterprise-scale performance, and long track record make it a strong choice for mission-critical data integration workloads.

Need help with your Dynamics 365 project?

I can help you apply these practices in your own Dynamics 365 environment.