I have couple of SSIS packages that were created on SQL Server 2012. I have been able to import them into SQL Server 2019; however, when I execute the package from my .NET application (.NET 4.5), I get the following error:

The server encountered an error processing the request. The exception message is 'Could not load file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.'. See server logs for more details.

I've installed SSDT 2012 & also SSDT 2019. I also upgraded the packages from 2012 to 2019 (on the newer SQL Server) and yet it wouldn't work.

I've also manually moved the dll to the following path on the server

C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies

Can someone please help me figure what is going on here?

CodePudding user response：

tl;dr;

You need to the install SQL Server Integration Services Service from the SQL Server install on the App server. This is not free though so you will most likely need to re-architect how things work to have the packages run on an already licensed instance of SQL Server.

The fine print

There are two means of executing SSIS packages.

The first is for development which takes place in Visual Studio with the SSIS templates installed. This adds the a debugging layer in so you can troubleshoot the packages, visually inspect them and such. This only works in Visual Studio when run in debug mode. Run package without debugger fails because the computer is only licensed for development purposed.

The second is systematic execution. This is all other use cases. Whether you run it via the command line utilities, a SQL Agent job, custom application using the Microsoft SQL Server assemblies, etc, it's all based on using the SQL Server Integration Services components.

I've also manually moved the dll to the following path on the server

So at this point, you're attempting to get an SSIS package to run on another server but it doesn't have all the assemblies there. The resolution is to use the SQL Server Installation media, Standard or Enterprise Edition, and install the SQL Server Integration Services Service on the application server. The installer knows what all to to install and where.

And, most importantly, you are going to be in compliance with the licensing from Microsoft---Well, once you pay for those licenses.

Your problem solving approach of copying assemblies and eventually making registry edit changes will finally result in your packages running from your applications but your employers business is now at risk from an audit with Microsoft Licensing. And the findings of that audit won't be "Whoopsie, you forgot to buy this license," it will be "You knowingly and willfully violated the T&Cs by manually installing these bits and are going to be fined out of existence"

Feel free to read the licensing guide

CodePudding user response：

A better plan is to install the packages in the SSIS Catalog on the SQL Server and invoke them remotely with the SSIS Catalog Stored Procedures

EG:

Declare @execution_id bigint

declare @folder_name nvarchar(200) =N'<folder name>';
declare @project_name nvarchar(200) =N'<project name>';
declare @package_name nvarchar(200) = N'<package name>.dtsx';
declare @wait_for_package_completion int = 1;
DECLARE @logging_level smallint = 3 --3 is verbose

EXEC [SSISDB].[catalog].[create_execution] @package_name=@package_name,
                                           @execution_id=@execution_id OUTPUT,
                                           @folder_name=@folder_name,
                                           @project_name=@project_name,
                                           @use32bitruntime=False,
                                           @reference_id=1,
                                           @useanyworker=True,
                                           @runinscaleout=True
Select @execution_id

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  
                                                        @object_type=50,
                                                        @parameter_name=N'LOGGING_LEVEL',
                                                        @parameter_value=@logging_level

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  
                                                        @object_type=50,
                                                        @parameter_name=N'SYNCHRONIZED',
                                                        @parameter_value=@wait_for_package_completion

EXEC [SSISDB].[catalog].[start_execution] @execution_id,  @retry_count=0

IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id)
  RAISERROR('The package failed. Check the SSIS catalog logs for more information', 16, 1)