Configuration changes needed for running SSIS 2008/2008 R2 classes in .NET4.0

  • Article

Recently we came across a case where the customer was developing a .NET managed application using SQL Server Integration Services (SSIS) object model.  The managed application was behaving differently in different versions of Visual Studio (2008 vs 2010). We finally figured out the whys. Rather than let the good research go to waste, we're posting the results here. 

Quick background

The managed assemblies that are commonly used when programming Integration Services using the .NET Framework are:

Microsoft.SqlServer.ManagedDTS.dll
Microsoft.SqlServer.RuntimeWrapper.dll
Microsoft.SqlServer.PipelineHost.dll
Microsoft.SqlServer.PipelineWrapper.dll

These assemblies contain various namespaces, such as at https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx

Code Segment

maincode

Note: This example requires a reference to the Microsoft.SqlServer.ManagedDTS.dll and Microsoft.SqlServer.SQLTask.dll assemblies.

For reference: https://msdn.microsoft.com/en-us/library/ms345167.aspx

Behavior Differences in VS2008 vs VS2010

When you run the above code in Visual Studio 2008 and in Visual Studio 2010 you will get different results (the value will be null for the object myExecuteSQLTask for VS 2010) as shown below:

VS 2008:

VS2008

VS 2010:

VS2010

The Solution:

The reason of this behavior is a mismatch of .NET framework between Visual Studio 2010 and those managed assemblies (Microsoft.SqlServer.ManagedDTS.dll, Microsoft.SqlServer.RuntimeWrapper.dll, Microsoft.SqlServer.PipelineHost.dll, Microsoft.SqlServer.PipelineWrapper.dll) that come with SQL 2008/2008 R2. Visual Studio uses .NET Framework 4.0 whereas those assemblies are compiled against .NET Framework 3.5.

The .NET application configuration can be used to handle this. If your application is built with the .NET Framework 4 but has a dependency on a mixed-mode assembly built with an earlier version of the .NET Framework we should use <supportedRuntime> Element in the configuration file. This element specifies which versions of the common language runtime the application supports. In addition, in the <startup> element in configuration file, we must set the useLegacyV2RuntimeActivationPolicy attribute to true. However, setting this attribute to true means that all components built with earlier versions of the .NET Framework are run using the .NET Framework 4 instead of the runtimes they were built with.

Reference: https://msdn.microsoft.com/en-us/library/bbx34a2h.aspx

Placing the following section in the configuration file of the application should take care of this issue:

<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
    <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>

 

Author : Faiz(MSFT), SQL Developer Engineer; Enamul(MSFT), SQL Developer Technical Lead