Visual Studio 2010 solution build process give a warning about indirect dependency on the .NET Framework assembly due to SSIS references

项目
08/18/2010

Here’s how I saw the problem…

Install SQL Server 2005. Afterwards, Install SQL Server 2008. (Integration Services or Management tools features are enough usually)
Install your favorite new tool Visual Studio 2010.
Create or Open a solution for your C# or VB application. The Solution properties should target the “.Net Framework 3.5” to have this problem.
Add references to one or more of the SSIS dlls in the SDK folder. These assemblies may be referenced from References tree in the Solution Explorer pane of Visual Studio 2010, by browsing to the dll files in the 32-bit directories from either SQL Server 2005 or SQL Server 2008:

1. On a 64-bit platform:
  C:\Program Files (x86)\Microsoft SQL Server\90\SDK\Assemblies
  C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies
2. On a 32-bit platform:
  C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
  C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
Microsoft.SqlServer.Dts.Design.dll
Microsoft.SQLServer.ManagedDTS.dll
Microsoft.SqlServer.ScriptTask.dll
Microsoft.SqlServer.SQLTask.dll
Microsoft.SqlServer.TxScript.dll
Microsoft.SqlServer.VSTAScriptingLib.dll

Build menu > Build ApplicationName.
The warnings will appear in the Error List pane.

The build of the .Net application may report the following Warnings in the Error List pane:

The primary reference "Microsoft.SqlServer.Dts.Design, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" could not be resolved because it has an indirect dependency on the .NET Framework assembly "mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" which has a higher version "2.0.3600.0" than the version "2.0.0.0" in the current target framework.

The primary reference "Microsoft.SQLServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" could not be resolved because it has an indirect dependency on the .NET Framework assembly "mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" which has a higher version "2.0.3600.0" than the version "2.0.0.0" in the current target framework.

The primary reference "Microsoft.SqlServer.ScriptTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" could not be resolved because it has an indirect dependency on the .NET Framework assembly "mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" which has a higher version "2.0.3600.0" than the version "2.0.0.0" in the current target framework.

The primary reference "Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86" could not be resolved because it has an indirect dependency on the .NET Framework assembly "mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" which has a higher version "2.0.3600.0" than the version "2.0.0.0" in the current target framework.

The primary reference "Microsoft.SqlServer.TxScript, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" could not be resolved because it has an indirect dependency on the .NET Framework assembly "mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" which has a higher version "2.0.3600.0" than the version "2.0.0.0" in the current target framework.

The primary reference "Microsoft.SqlServer.VSTAScriptingLib, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL" could not be resolved because it has an indirect dependency on the .NET Framework assembly "mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" which has a higher version "2.0.3600.0" than the version "2.0.0.0" in the current target framework. WindowsFormsApplication1

This problem affects machines where SQL Server 2005 was once installed. Even if SQL Server 2008 or SQL Server 2008 R2 is currently installed, if at some point SQL Server 2005 was installed in the past, the problem assembly may exist in the Globally Assembly Cache (GAC).

What caused it?

The problem is related to the way the aforementioned assemblies reference the SQL Server 2005 version of Microsoft.SQLServer.msxml6_interop.dll. That msxml_interop assembly in turn references mscorlib.dll that is version 2.0.3600, which is the prerelease Beta 2 of .NET 2.0.

SSIS uses the MSXML6 interop assembly for SSIS managed components and tasks for serialization (when calling LoadFromXML). Other places that use MSXML6 explicitely for the lookup GUI and SQL Task in SSIS.

Visual Studio 2010 warns about references to that beta build of the 2.0 .Net core library, and the warning appears during the build action.

What’s really interesting is that the SQL Server 2008 copy of Microsoft.SQLServer.msxml6_interop.dll does not have this problem, but people still have this problem after SQL Server 2008 is installed. The twist is if you install SQL Server 2008 on a machine where SQL Server 2005 is currently or previously installed, the problem file does not get upgraded during setup to the corrected version of the file because its already in the GAC, and it has the same version 6.0.0.0 (since the version didn’t change between 2005 and 2008).

My Setup logs confirm this behavior:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<date>
        Sql_bids_Cpu64_1.log
            MSI (s) (10:A4) [16:04:43:963]: Verifying accessibility of file: Microsoft.SqlServer.msxml6_interop.dll

        sql_engine_core_shared_Cpu64_1.log
            MSI (s) (10:7C) [15:46:57:096]: Verifying accessibility of file: Microsoft.SqlServer.msxml6_interop.dll

        sql_is_Cpu64_1.log
            MSI (s) (10:B8) [15:55:32:658]: Verifying accessibility of file: Microsoft.SqlServer.msxml6_interop.dll

        sql_ssms_Cpu64_1.log
            MSI (s) (10:10) [15:56:31:406]: Verifying accessibility of file: Microsoft.SqlServer.msxml6_interop.dll

        Sql_tools_Cpu64_1.log
            MSI (s) (10:B0) [15:58:21:572]: Verifying accessibility of file: Microsoft.SqlServer.msxml6_interop.dll

Therefore due to this twist, you may also have the problem if SQL Server 2008 is presently installed on the same machine as Visual Studio 2010, if SQL Server 2005 was ever installed on the same machine.

What’s inside the Culprit File that causes this problem?

Open it in .Net Reflector to see the dependencies… the rogue reference to mscorlib.dll that is version 2.0.3600, which is the prerelease Beta 2 of .NET 2.0!

For comparison, the SQL 2008 file from 100\DTS\Binn doesn’t have this rogue dependency. v 2.0.0.0 is the corrected RTM of .Net framework 2.0 as shown below:

How can you get rid of the warnings?

Update the version of the Microsoft.SQLServer.msxml6_interop.dll in the GAC with a copy of the file from SQL Server 2008 installation that has the correct references to .Net 2.0 RTM (ie. get rid of the beta reference).

Install a copy of the single SQL Server 2008 file Microsoft.SQLServer.msxml6_interop.dll into the GAC on the machine where Visual Studio 2010 is used to build the solutions.

On Windows Vista, Windows 7, Windows 2008 and WIndows 2008 R2, the GAC can be seen in folder C:\Windows\Assembly. You may drag and drop the file Microsoft.SQLServer.msxml6_interop.dll into the GAC folder C:\Windows\Assembly.

Easier said than done!

Problem 1: One complication is that you may receive the following error on some operating systems when assemblies in the GAC are protected by UAC.

Problem 2: Because this file is installed by the SQL installer (Microsoft Installer) it is locked by windows so you cannot remove the old version from the GAC until you remove the registry value associated with it. Therefore you must manually edit the registry to unprotect this file.

Access is denied: 'Microsoft.SqlServer.msxml6_interop.dll'.

Steps to Resolve

Let me know if you have problems with these, so I may correct any mistakes…

1. Open the Registry Editor:

Start > Run > Regedit

Locate the following key and value:

HKEY_CLASSES_ROOT\Installer\Assemblies\Global
Microsoft.SqlServer.Msxml6_interop,fileVersion="6.0.0.0",version="6.0.0.0",culture="neutral",publicKeyToken="89845DCD8080CC91",processorArchitecture="MSIL"

Temporarily rename the registry value name (left column in rightmost pane) by adding an underscore '_' in front of the name.

Example: _ Microsoft.SqlServer.Msxml6_interop,fileVersion="6.0.0.0",version="6.0.0.0",culture="neutral",publicKeyToken="89845DCD8080CC91",processorArchitecture="MSIL"

After rename…

2. Determine if you have more than one copy of Microsoft.SQLServer.msxml6_interop.dll in the GAC. If there is only one copy proceed to step 3 to install the new copy. If you have two copies, you may need to delete the old one first.

Start > Run > Cmd (if you are missing the Run box, just type it in the search box that’s there)
dir /s C:\Windows\assembly\*Microsoft.SqlServer.msxml6_interop.dll

A. Review the output from dir. If you only have one .dll file in the dir output, proceed to the next step 3, skipping step 2B.

B. Review the output from dir. If and only if you have two copies of Microsoft.SQLServer.msxml6_interop.dll in the GAC then you can remove the oldest one from the GAC. If you have more than one copy of this file in the GAC and if the "Last Modified" date of the file is before year 2006 (it should be dated in 2005).

{

Note: Both the good and bad copy of the file have the same version number 6.0.0.0, so version alone is not a good indicator of which file can be used to workaround the problem.

Then… Manually remove the SQL 2005 version of Microsoft.SqlServer.msxml6_interop.dll from the GAC. There are 3 possible approaches:

ALTERNATIVES (don’t need to do all of these)

i. Right click on the assembly in the C:\Windows\Assembly folder to remove it, or use the gacutil command line utility that ships with the SDK. This fails with Access Denied on UAC enabled machines though :-(

ii. Gacutil command line tool from an UAC elevated command prompt. Locate the Gacutil.exe utility in one of the Microsoft SDK folders…

You can use the Visual Studio command prompt to easily find gacutil. Start > Programs > Microsoft Visual Studio 2010 > Visual Studio Tools > Visual Studio Command Prompt (2010). Make sure to run as Administrator (UAC nag)

Run this command to uninstall the extra copy from the GAC
Gacutil /uf Microsoft.SQLServer.msxml6_interop

If needed, you can manually find the GACUtil in folders such as C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\x64 or C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin

iii. Only if Gacutil fails… brute force delete of the assembly from the GAC. From an elevated command prompt, delete the MSIL folder that contains the problem assembly as follows:
rmdir C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Sqlserver.msxml6_interop\6.0.0.0__89845dcd8080cc91\ /s

}

3. Locate the replacement file from a SQL Server 2008 installation folder under 100\DTS\Binn on a machine where SQL Server 2008 has been installed, but SQL 2005 was not installed.

On a 32-bit machine check the folder: "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\Microsoft.SqlServer.msxml6_interop.dll"
On a 64-bit machine check the folder: "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\Microsoft.SqlServer.msxml6_interop.dll"

You have to find the good one under 100\DTS\Binn. The file from 90\DTS\Binn is the bad one.

Note: this file does not get installed if SQL Server 2005 was already installed on the machine at the time when SQL Server 2008 was installed. Therefore, you may only find this file if you have a separate machine where SQL Server 2008 is installed without SQL Server 2005 having been present.

4. Install the SQL Server 2008 file into the GAC via GacUtil.exe command line.

{

Install the new Microsoft.SqlServer.msxml6_interop.dll from SQL2008 using the GacUtil.exe utility.

You can drag and drop the file into C:\Windows\Assembly on some OSes, but I get an Access denied error, so I have to use GACUtil.

Finding Gacutil is always too difficult… you can use the Visual Studio command prompt to easily find gacutil.

Start > Run > Microsoft Visual Studio 2010 > Visual Studio Tools > Visual Studio Command Prompt (2010).

Alternatively you can manually locate the Gacutil.exe utility in one of the Microsoft SDK folders, such as:
C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\x64 or C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin

Run the Installation of the 2008 assembly:

Gacutil /i "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\Microsoft.SqlServer.msxml6_interop.dll"

The successful output is "Assembly successfully added to the cache"

}

5. Reverse the temporary rename of the registry key from step 1 above to protect the file from accidental change in the future.

Open the Registry Editor:

Start > Run > Regedit