Balanced Data Distributor

gravatar

Paul S. Waters

This snippet shows how to use the Balanced Data Distributor by Microsoft.

published 07.29.13

last updated 07.29.13


Share

Tags

  • Balanced Data Distributor
  • BDD,
                            


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Annotations>
		<Annotation AnnotationType="Documentation" Tag="DestTable">
            --Destination table for sample: 
             
			CREATE TABLE [dbo].[BusinessEntity](
				[BusinessEntityID] [int] NOT NULL,
				[rowguid] [uniqueidentifier] NOT NULL,
				[ModifiedDate] [datetime] NOT NULL
			)
        </Annotation>
		<Annotation AnnotationType="Documentation" Tag="Sql2008">
			ComponentClassId="487806FB-4C3B-4DCD-B91D-62CAAE06D460"
        </Annotation>
		<Annotation AnnotationType="Documentation" Tag="Sql2012">
			ComponentClassId="f06c606c-5980-460c-b2af-b0eca031b7a4"
        </Annotation>
    </Annotations>
	<Connections>
        <Connection Name="AdventureWorks" ConnectionString="Provider=SQLNCLI10;Data Source=.;Initial Catalog=AdventureWorks2008R2;Integrated Security=SSPI;" />
    </Connections>
	<Databases>
        <Database Name="AW" ConnectionName="AdventureWorks" />
    </Databases>
	<Schemas>
        <Schema Name="dbo" DatabaseName="AW" />
    </Schemas>
    <Packages>
        <Package Name="MS_BDD" Language="None" ConstraintMode="Parallel" >
            <Tasks>
				<ExecuteSQL Name="SQL TRUNCATE dbo_BusinessEntity" ConnectionName="AdventureWorks">
                    <DirectInput>TRUNCATE TABLE [dbo].[BusinessEntity]</DirectInput>
                </ExecuteSQL>
                <Dataflow Name="Data Flow Task">
					<PrecedenceConstraints>
                		<Inputs>
                			<Input OutputPathName="SQL TRUNCATE dbo_BusinessEntity.Output" />
                		</Inputs>
                	</PrecedenceConstraints>
                    <Transformations>
                        <OleDbSource Name="OLE DB Source" ConnectionName="AdventureWorks">
                            <ExternalTableInput Table="[Person].[BusinessEntity]" />
                        </OleDbSource>
                        <CustomComponent Name="Balanced Data Distributor" ComponentClassId="487806FB-4C3B-4DCD-B91D-62CAAE06D460" ComponentTypeName="Balanced Data Distributor" ContactInfo="Balanced Data Distributor;Microsoft Corporation; Microsoft SqlServer; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">
                            <InputPaths>
                                <InputPath OutputPathName="OLE DB Source.Output" Identifier="Balanced Data Distributor Input"></InputPath>
                            </InputPaths>
                            <OutputPaths>
                                <OutputPath Name="Balanced Data Distributor Output 1" ErrorOrTruncationOperation="" SynchronousInput="Balanced Data Distributor Input" ExclusionGroup="1" />
                                <OutputPath Name="Balanced Data Distributor Output 2" ErrorOrTruncationOperation="" SynchronousInput="Balanced Data Distributor Input" ExclusionGroup="1" />
                            </OutputPaths>
                        </CustomComponent>
                        <OleDbDestination Name="OLE DB Destination" ConnectionName="AdventureWorks">
                            <InputPath OutputPathName="Balanced Data Distributor.Balanced Data Distributor Output 1" />
                            <TableOutput TableName="AW.dbo.BusinessEntity" />
                        </OleDbDestination>
                        <OleDbDestination Name="OLE DB Destination 1" ConnectionName="AdventureWorks">
                            <InputPath OutputPathName="Balanced Data Distributor.Balanced Data Distributor Output 2" />
                            <TableOutput TableName="AW.dbo.BusinessEntity" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

To use the sample as-is:

  1. Create [dbo].[BusinessEntity] in your AdventureWorks2008R2 database.
  2. If needed, edit the AdventureWorks connection.
  3. If using SSIS 2012, replace the 2008 ComponentClassId with the one in the Sql2012 annotation.

Information on the Balanced Data Distributor from Microsoft can be found at: http://blogs.msdn.com/b/sqlperf/archive/2011/05/25/the-balanced-data-distributor-for-ssis.aspx

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

enders

7:28am 08.02.13

Excellent stuff, unfortenately the script gives an error. Description : The 'ComponentClassID' attribute is not valid for 'CustomComponent' element. Recommendation : Remove the invalid attribute What goes wrong ?

gravatar

rene

1:10pm 11.12.13

I get the same error the ComponentClassId is not supported. If i remove it it tries to look for the sql server 2008 r2 version. I use sql server 2012

gravatar

Mark55

3:39pm 07.26.17

I know these comments are a bit old, but for anyone browsing around on this issue, the trick is to create the component manually in a package and copy the id from the properties panel. So for example. the ComponentClassId for the Balanaced Data Distributor in SQL 2016 is C925C2DD-D414-4F4D-942E-F4F3E4871666