Homemade SSIS load balancer for large data loads

When you need to import hundreds of thousands records, it can take a long time to import with the KingswaySoft destination. This can be a serious issue, if a large data load is part of a scheduled running job. For this reason, it can make a lot of sense, to balance the load across multiple application users or service users.

The method I use to create a balanced distribution of load, is with the Condition Split component. Before setting up the Conditional Split, we need get a Rowindex. If you are using a SQL source, this can be achieved with the following:

ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowIndex

The new column RowIndex (can be renamed to whatever you like) displays a rowindex, which we need to use in the Conditional Split.
If you are using a CRM source, you can get a rowindex using the KingswaySoft ProductivityPack. In the Premium Derived Column, there is a function called RowIndex(), which also returns the rowindex.

For each service user / application user you can use, you create a row in the conditional split. You can add as many rows as you like.

For my example, I have five application users. For each of them, I create a modulo for the rowindex.
RowIndex % 5 == 0
The % # is the total of available connections, and after the == is just from 0 to 4.

This creates five evenly distributed outputs from the Conditional Split, which can be sent to individual CRM destinations. Just remember to set the Destination connections to different users. The only difference with the destinations is the connection – they are setting the same field values.