- I have code that executes SSIS packages in.Net 2.0 using C# and it's compiled fine until rebuilding my development machine from scratch. The compiler can no longer find the sub packages 'Dts.Runtime' in the 'Microsoft.SqlServer' package.
- Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure. End Enum ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts property. Connections, variables, events, ' and logging features are available as members of the Dts property as shown in the following examples.
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.
Microsoft.SqlServer.Dac - Microsoft.Data.Tools.Schema.Sql - Microsoft.Data.Tools.Utilities These libraries provide the classes you can use in your code to perform operations on DACPAC and BACPAC packages. These packages can be extracted and exported from existing databases or used to deploy and import new databases.
Quick background
Manageddts
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 http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx
Code Segment
Note: This example requires a reference to the Microsoft.SqlServer.ManagedDTS.dll and Microsoft.SqlServer.SQLTask.dll assemblies.
For reference: http://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:
VS 2010:
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.
Microsoft.sqlserver.dts.runtime.application
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: http://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>
Microsoft.sqlserver.dts.runtime.package
Author : Faiz(MSFT), SQL Developer Engineer; Enamul(MSFT), SQL Developer Technical Lead