SSIS Build Performance

After a while we detected a low performance executing build of SSIS solution with the MsBuild extension of CodePlex (https://archive.codeplex.com/?p=sqlsrvintegrationsrv) .

Doing some debug with Visual Studio and with the help of Sysinternals tools we reached a diagnostic.

When using SSDT 2015 (SQL Server Data Tools) calling the method Add of the Microsoft.SqlServer.Dts.Runtime.ConnectionManagerItems class generates an insane lookup in the registry for the following pattern: HKCR…\CLSID…\Implemented Categories{GUID}.

The GUID changes according to the type of connection provider you are adding, the lookup is going to look all COM objects installed in the machine to see if the implement the category needed.

With Process Monitor, I detected approximately 25 Million lookups in the registry trying to find the above pattern, this number is indexed to the number of COM installed in the server.

The Implemented Categories probing is done for all connections in the VS Solution and again for all connections referenced or used in dtsx files.

Because the company I work in has Microsoft support we asked help with this issue, they classified this as a bug, but the version of this product is not under support, only for security bugs. We are going to have to live with this bug, but not for a long time as we are in progress with the upgrade of our SQL Server products.