SSIS – Balanced Data Distributor Transformation

Balanced Data Distributor – Transformation

Installation:

Source: http://www.microsoft.com/download/en/details.aspx?id=4123

1. Downloaded 32 bit – BalanedDataDistributor-x86.msi

BDD Download

2. Installation process will start and will register the .dll of the component

BDD Installation

After Installation

1. Open Visual Studio
2. Create SSIS project
3. Toolbox –> Right Click –> Choose Items
4. SSIS Data Flow Items –> Balanced Data Distributor

BDD into Toolbox

Balanced Data Distributor available in Toolbox under Data Flow Transformations

BDD Transformation

SSIS Package Development:

Test cases:

1. Normal Load Test1 – Load data from source to target without using BDD.
2. BDD Load Test2 – Load
   data from source to target with BDD and two destination components to same target table.
3. BDD Load Test3 – Load data from source to target with BDD and three destination components to same target table.

Databases:

Source DB : AdventureWorks, Table : Sales.Customer

Destination DB : SANDBOX  for testing created this DB, target table schema same as Sales.Customer

SSIS – Package details:

1. Created BalancedDataDistributor project
2. BalancedDataDistributor.dtsx package

BalancedDataDistributor Package

In all my test cases, my target is to test how the distribution of data through BDD and time it has taken to transfer data from source to destination table.

Planning:

1. Considering only screen shots while Data Flow components executing
2. Data reconciliation and time calculations from the SSIS data flow.

Results:

Test Case 1:

1. Executing the “DF Normal Load Test1” component.
2. In the data flow of the component, to get data count – 19,185
3. To get the Elapsed time of the process, time to transfer the data from source to target table – 2.558 sec

DF Normal Load Test1

DF Normal Load Test1 - Elapsed time

Test Case 2:

1. Executing the “DF BDD Load Test2” component.
2. In the data flow of the component, to get data count, Balanced Data Distributor Output 1- 9,830 and Balanced Data Distributor Output 2- 9,355
3. To get the Elapsed time of the process, time to transfer the data from source to target table – 0.718 sec

DF BDD Load Test2

DF BDD Load Test2 - Elapsed time

Test Case 3:

1. Executing the “DF BDD Load Test3” component.
2. In the data flow of the component, to get data count, Balanced Data Distributor Output 1- 9,830, Balanced Data Distributor Output 2- 9,355 and Balanced Data Distributor Output 3 – 0
3. To get the Elapsed time of the
   process, time to transfer the data from source to target table – 0.624 sec

DF BDD Load Test3

DF BDD Load Test3 - Elapsed time

Stats:

1. Data distribution: Data distribution  is not even on both data flow paths, we could expect almost equal distribution  of data.
2. Performance of approx. more than 60%  when using BDD
3. When using three BDD output to  destination, we couldn’t see any data transfer through 3^rd path, the  data flow doesn’t use the 3^rd resource hence whenever the data flow required a parallel processing only the flow will be used otherwise it may not be used

Points to consider:

1. BDD works well with parallelism hardware so we could use these results for understanding only.
2. The performance of data flow may also varies with hardware configurations, this test developed on personal
   laptop with 4GB RAM, Dual Core i7 processor (normal laptop)
3. Implementation of BDD is simple as user doesn’t require setup any options except the paths to target components.

For any additional info, please refer to reference list –

Reference: http://blogs.msdn.com/b/sqlperf/archive/2011/05/25/the-balanced-data-distributor-for-ssis.aspx