A new transform component promises faster data transformations for SQL Server Integration Services.

If you’re using SQL Server Integration Services (SSIS) for complex data flows to data cubes, for example, a new transform component has been released. The Balanced Data Distributor (BDD) makes more effective use of multi-processor and multi-core servers by taking your input data and routing it in equal proportions through the outputs you’ve defined. Those of you running complex data transforms will know only too well just how slow this process can be. BDD also works on data buffers rather than data rows, so it should be efficient.

BDD essentially gives you an easy way to create independent segments of an SSIS data flow, and SSIS then distributes the work across multiple threads. If (as is likely) you’re running SSIS on a multi-core server with the destination database distributed across disks, you could see a real jump in performance.

There’s a good summary of what BDD is and what it can do for you on this MSDN SQL Server Performance blog. Len Wyatt of the SQL Server Performance Team says on the blog that BDD may offer an advantage if:

1.There is a large amount of data coming in.

2.The data can be read faster than the rest of the data flow can process it, either because there is significant transformation work to do or because the destination is the bottleneck. If the destination is the bottleneck, it must be parallelizable.

3.There is no ordering dependency in the data rows. For example if the data needs to stay sorted, don’t go and split it up using BDD.

Of course, the mere mention of SSIS may well be making your blood boil, especially if you’re working with BIDS. Business Intelligence Development Studio is Microsoft Visual Studio 2008 with additional project types that are specific to SQL Server business intelligence, and it’s the main way to develop business solutions that include Analysis Services, Integration Services, and Reporting Services projects.

The key to the blood boiling lies in the mention of VS 2008, because Visual Studio 2010 doesn’t support BIDS, so if you want to develop in BIDS you have to install Visual Studio 2008 alongside Visual Studio 2010 and work on your BIDS projects in Visual Studio 2008. You’d think that when companies are paying up to $60,000 per core for SQL Server Microsoft might have got their act together to get an up to date version of BIDS for developers. The situation should be made better when SQL Server ‘Denali’ is released, but if you’re suffering because of this in the meantime, I recommend you vote up this request for a SSIS VS2010 project type.