Home > SSIS > SSIS – Balanced Data Distributor Transformation

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

BDD Download

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

BDD Installation

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

BDD into Toolbox

Balanced Data Distributor available in Toolbox under Data Flow Transformations

BDD Transformation

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

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

DF Normal Load Test1 - Elapsed time

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

DF BDD Load Test1 - Elapsed time

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

DF BDD Load Test3 - Elapsed time

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 3rd path, the  data flow doesn’t use the 3rd 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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: