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

Dung DinhDBA and Business Intelligence Developer
CERTIFIED EXPERT
Published:
Updated:
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.

 

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Dung DinhDBA and Business Intelligence Developer
CERTIFIED EXPERT
0
3,112 Views

Comments (2)

CERTIFIED EXPERT

Commented:
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.
Dung DinhDBA and Business Intelligence Developer
CERTIFIED EXPERT

Author

Commented:
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,

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
Continue Growing Your Skills and Your Career
  • Interact with leading experts on your specific technology problems.
  • Receive the guidance of experienced professionals.
  • Learn from troubleshooting others have experienced.
  • Gain knowledge from a library of courses, all included.