CODEWITHSUNDEEP
.netsql-serverssisbusiness-intelligence
Alan Macdonald
Alan Macdonald

Reputation: 1900

Deploying SSIS Package to Server Causes Error

I have developed a C# Console Application Project to run an SSIS package and I am using .Net DTS library to execute:

using Microsoft.SqlServer.Dts.Runtime;
.
.
var app = new Application();
var package = app.LoadPackage(@"ACE.dtsx", null);
package.Execute();
foreach (var error in package.Errors)
{
   logger.Error(error.ErrorCode + " " + error.Description);
}

I have this all working locally from Visual Studio and no errors are reported from executing the packge.

However when I deploy to a server with Integration Services installed as verified by SQL Server Configuration Manager and also Windows Services showing the SQL Server Integration Service I got a different error depending on whether I deploy in x86 or x64/Any CPU platforms.

x86

When I set my .Net library to build as x86 and deploy I get the following error on the line:

var app = new Application();

ERROR: Error occurred during master control flow Microsoft.SqlServer.Dts.Runtime.DtsComException: An Integration Services class cannot be found. Make sure that Integration Services is correctly installed on the computer that is running the application. Also, make sure that the 64-bit version of Integration Services is installed if you are running a 64-bit application. ---> System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {C3EC6BC0-D544-47D5-A0F2-2825E47DBE24} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). at Microsoft.SqlServer.Dts.Runtime.Application..ctor()

This seems to suggest it can't find the integration service or libraries on the server. This makes sense to me since the server is running 64bit Windows with 64bit SQL Server.

x64/Any Cpu

If I deploy with x64 or Any Cpu platforms the error becomes:

ERROR: Error occurred during master control flow System.BadImageFormatException: Could not load file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An attempt was made to load a program with an incorrect format. File name: 'Microsoft.SqlServer.DTSRuntimeWrap, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

Which does not make sense to me since the library is now 64bit which matches the server.

Environments

Local:

Server:

I was going to install SQL Server 2012 locally so my version matched the server but then I realised it's not that version that matters, it's the Visual Studio Data Tools for Business Intelligence since that's giving me the .Net wrapper classes and as far as I can tell I don't have a choice with VS2013 what version to use. Besides this is surely compatible with older SQL Server versions.

The Data Tools for Business Intelligence is an x86 installer but I was led to believe this was purely because of Visual Studio running as x86 and does not impact x64 runtime support.

How do I deploy successfully?

Upvotes: 2

Views: 2325

Answers (1)

billinkc
billinkc

Reputation: 61211

Development

  • SQL Server 2014 Enterprise Edition 64bit with all services including integration installed.

Server

  • SQL Server 2012 x64 with Integration Services

The GUID for Application as reported by your invoker is C3EC6BC0-D544-47D5-A0F2-2825E47DBE24 - that's the SQL Server 2014 version. But, when you go to run it, it can't find it because the actual server only has 2012 installed which would be 5572B772-88AC-4A35-96F8-E28626AC7E8F

You need to perform an installation on your Local instance to have SQL Server 2012 SQL Server Integration Services Service (Developer edition is fine) set up, possibly SQL Server Data Tools but I don't know that.

That should get you all the DLLs you currently have for the 2014 in a 2012 flavor. Then you get to go through the process of using the SQL Server 2012 version of all the 2014 assemblies.

From a licensing perspective, do realize that the installation of the Integration Services Service on the Server itself counts as a licensed instance of SQL Server. Whenever you true up for your licensing, that might be a painful and costly explanation of why you have a per-core based licensed product just to run SSIS packages. With the Project Deployment Model in SQL Server 2012, if you have the actual database installed somewhere, you might look to see whether the invocation of packages via stored procedure calls might also serve your needs without the additional cost of licensing a separate server.

Upvotes: 2

Related Questions