如何通过KingswaySoft for SSIS突破5000条限制，实现CRM数据获取与迁移？

Hey there, let's break down how to tackle both your retrieval and migration questions—they both tie back to Dynamics 365's built-in 5000-record OData pagination limit (this is a CRM constraint, not a hard limit from KingswaySoft itself). Here's what you need to do:

1. Retrieving More Than 5000 Records from CRM #

KingswaySoft's Dynamics 365 Source component is designed to handle pagination automatically—you just need to confirm a few settings:

• Open your Dynamics 365 Source component, go to the General tab, and make sure Enable Paging is checked (this is enabled by default, but it's worth verifying).
• Set the Page Size to 5000 (the maximum allowed by Dynamics 365; any higher value will be ignored by the CRM API).
• For large datasets, consider adding a filter to narrow down results incrementally (e.g., modifiedon gt @[User::LastSuccessfulSync]). This not only speeds up retrieval but also avoids pulling unnecessary historical data if you're doing incremental loads.

2. Migrating Any Number of Records Between Two CRM Databases #

To migrate an arbitrary number of records, you have two reliable approaches—one that's hands-off, and another for fine-grained control:

Approach 1: Automatic Pagination & Batch Writing (Simplest) #

This works for most use cases and requires minimal setup:

• Use a Dynamics 365 Source (connected to your source CRM) with paging enabled (as above). It will automatically loop through all pages of data until no more records are left.
• Connect the source to a Dynamics 365 Destination (connected to your target CRM). In the destination's General tab, set Batch Size to 5000 (again, the CRM API's max batch limit for writes).
• The destination will automatically batch records into 5000-unit chunks and write them to the target CRM. The entire flow will handle all records, no matter how large n is.

Approach 2: Manual Batch Processing (For Large/Complex Datasets) #

If you need more control over batch logic (e.g., avoiding performance hits from large $skip operations), use this method:

• Step 1: Calculate Batch Boundaries
  Use an Execute SQL Task or a Dynamics 365 Source to run an aggregate query to get the total number of records, or define ranges using a unique, ordered field (like accountid or createdon). For example, a FetchXML count query:

  <fetch aggregate='true'>
    <entity name='account'>
      <attribute name='accountid' aggregate='count' alias='total_records'/>
    </entity>
  </fetch>
  

  Store the total in an SSIS variable (e.g., @[User::TotalRecords]), then calculate the number of batches as (TotalRecords + 4999) / 5000.

• Step 2: Loop Through Batches
  Add a Foreach Loop Container to iterate over each batch. Use a variable to track the current batch number (e.g., @[User::CurrentBatch]).

• Step 3: Fetch Batch-Specific Records
  Inside the loop, configure the Dynamics 365 Source to pull only the current batch. Instead of using $skip (which can be slow for very large datasets), use a range filter on an ordered field:

  accountid ge @[User::BatchStartId] and accountid le @[User::BatchEndId]
  

  Calculate BatchStartId and BatchEndId based on the current batch number and your chosen field's range.

• Step 4: Write to Target CRM
  Connect the filtered source to the Dynamics 365 Destination, keeping the batch size set to 5000. Enable error handling on the destination to log failed records for later review.

Key Best Practices #

• Incremental Migration: Whenever possible, sync only new/updated records using modifiedon or createdon filters. This reduces load on both CRMs and speeds up the process.
• Limit Columns: Only select the fields you need to migrate—avoid pulling unnecessary data to reduce payload size.
• Error Handling: Configure the destination's error output to redirect failed records to a log table. This lets you retry failed records without re-running the entire migration.

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