In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).

My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text files by C# code, not the dtexec tool. One of my clients upgraded their system to SQL Server 2014 while other clients have kept the 2008 version, but are using the same 2008 SSIS packages. We don’t want to duplicate packages because it will take more time to manage (one for SQL 2008 and another for SQL 2014). My problem is “how to run them in both environments successfully.” Some components of 2008 SSIS refer to 10.0.0.0 version of .dll files such as Script Task component… which are not existed in new environment, so they will fail when run. We will see this error message (or something like it):

System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {BA785E28-3D7B-47AE-A4F9-4784F61B598A} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

After researching, I found out the solution to resolve this problem by inserting some configuration codes into .config file of our application.

<runtime> <gcServer enabled=”true”/> <disableCommitThreadStack enabled=”true”/> <generatePublisherEvidence enabled=”false”/> <assemblyBinding xmlns=”urn:schemas-microsoft-com:asm.v1″> <dependentAssembly> <assemblyIdentity name=”Microsoft.SqlServer.ManagedDTS” publicKeyToken=”89845dcd8080cc91″ culture=”neutral” /> <bindingRedirect oldVersion=”10.0.0.0-12.0.0.0″ newVersion=”12.0.0.0″/> </dependentAssembly> </assemblyBinding> <assemblyBinding xmlns=”urn:schemas-microsoft-com:asm.v1″> <dependentAssembly> <assemblyIdentity name=”Microsoft.SqlServer.DTSRuntimeWrap” publicKeyToken=”89845dcd8080cc91″ culture=”neutral” /> <bindingRedirect oldVersion=”10.0.0.0-12.0.0.0″ newVersion=”12.0.0.0″/> </dependentAssembly> </assemblyBinding> <assemblyBinding xmlns=”urn:schemas-microsoft-com:asm.v1″> <dependentAssembly> <assemblyIdentity name=”Microsoft.SqlServer.DTSPipelineWrap” publicKeyToken=”89845dcd8080cc91″ culture=”neutral” /> <bindingRedirect oldVersion=”10.0.0.0-12.0.0.0″ newVersion=”12.0.0.0″/> </dependentAssembly> </assemblyBinding> <assemblyBinding xmlns=”urn:schemas-microsoft-com:asm.v1″> <dependentAssembly> <assemblyIdentity name=”Microsoft.SqlServer.PipelineHost” publicKeyToken=”89845dcd8080cc91″ culture=”neutral” /> <bindingRedirect oldVersion=”10.0.0.0-12.0.0.0″ newVersion=”12.0.0.0″/> </dependentAssembly> </assemblyBinding> </runtime>

This configuration code will help our C# application redirect .dll version to new version 12.0.0.0 which is in SQL 2012 or higher although they are still using 2008 SSIS version. In this case, the application calls SSIS packages via dtexec tool, so you won’t face the issue because the dtexec tool uses new .dlll version.

The same issue will happen if you use C# to run XMLA files or .dll files for Analysis Services. You should insert configuration code in .config file of your application.

<runtime> <assemblyBinding xmlns=”urn:schemas-microsoft-com:asm.v1″> <dependentAssembly> <assemblyIdentity name=”Microsoft.AnalysisServices.AdomdClient” publicKeyToken=”89845dcd8080cc91″ culture=”neutral” /> <bindingRedirect oldVersion=”10.0.0.0″ newVersion=”12.0.0.0″ /> </dependentAssembly> </assemblyBinding> </runtime>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s