How to run 2008R2 SSIS packages on SQL Server 2014

Question

In current, I have a .NET application which is executing 2008R2 SSIS packages and the application are running on win server 2008r2 (64bit). These packages were built in BIDS 2008. After that, I upgraded my database server from SQL2008R2 to SQL2014 Standard (running on Win server 2012R2) and deployed .NET app to new environment.

An now, I'm encountering the issues with calling package from .NET application. The message error is ERROR: Could not load file or assembly "Microsoft.SqlServer.Dts.DTSRuntimeWrap.dll, Version=10.0.0.0, Culture=neutral, Public Key=..." or one of it's dependencies. An attempt was made to load program with an incorrect format.

Step 1: create a .NET simple app to call package (SSIS 2008). I also add 2 .dll version (10.0.0.0) for reference Microsoft.SQLServer.DTSRuntimeWrap and Microsoft.SQLServer.ManagedDTS allocated in C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

using Microsoft.SqlServer.Dts.Runtime;

using Application = Microsoft.SqlServer.Dts.Runtime.Application;


private void button1_Click(object sender, EventArgs e)
{

try
{

string pkgLocation = @".\Package.dtsx";

Application app = new Application();

Package pkg = app.LoadPackage(pkgLocation, null);

DTSExecResult pkgResults = pkg.Execute();

MessageBox.Show(pkgResults.ToString());

}
catch(Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
}
}

Step 2: deploy this app (included 2 .dll, the package) to new environment and execute it. The result is error message as above.

In new environment, 2 .dll is version 12.0.2000.8 allocate in C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies. And I guess that whenever the package is executed it also requires some functions of old .dll version 10.0.0.0 to execute their's components (Execute SQL Task, Task Script,...) but these .dll is not existed in new environment and it causes error.

So, how can I resolve the issue except upgrading SSIS packages to 2014 or any suggestions?

Many thanks,

Answer 1

Your 2008R2 packages need to be upgraded to 2014 since you did an upgrade of the server, rather than a side-by-side install. So there are two possibilities that I can think of:

1. Upgrade all the packages to 2014, this might be as simple as opening them up in SSDT-BI 2014 and saving them (not in the project model but as stand alone packages). It is doubtful that any code would be broken from R2 to 2014, there were many enhancements as opposed to fundamental changes.
2. Or install SSIS 2008R2 on your server again and have your .NET application call DTEXEC from the correct folder. i.e. on my machine it is in C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe. You can also change the path variable to make sure that the 2008 dtexec is on top of the 2014.