<

How to run 2008R2 SSIS packages on both SQL Server 2012/2014 and SQL Server 2008

Published on
5,557 Points
2,557 Views
Last Modified:
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>

Open in new window


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>

Open in new window



Updated: 19/12/2014:
I have just recieved comments that there is an issue of uploading file. Our application includes this feature allow to upload files to Internet.
They reported that this feature is failed, they could not upload file to their server. I worked with my team and found out the reason in my .config file


<gcServer enabled="true"/>

Open in new window

gcServer - DTEXEC uses the "server" version of garbage collector. Server GC performs better for typical SSIS load, especially on multiprocess machines. This is important if your SSIS package uses managed transforms (e.g. ADO.NET source, script transform, or custom transform written in .NET). 

However, this option is preventing  .NET codes which will extend the virtual memory in case you want to upload a big file.  Please verify your application if you enable this configuration.

 
0
Comment
Author:Dung Dinh
2 Comments
 
LVL 8

Expert Comment

by:Andrei Fomitchev
I had similar issues with DTS/SSIS and 32-bit/64-bit.
I re-implemented SSIS with C# and stored procedures - it has no dependencies from MS SQL Server version anymore.
0
 
LVL 9

Author Comment

by:Dung Dinh
Hi Andrei,

Your approach is the simplest but it only works if you have several SSIS packages.

How long will you spend if you have several hundres packages.

Thanks,
0

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month