SSIS External Package - Invalid Class String Error-CodePudding

I'm trying to call an SSIS package from another solution, but it gives me an Invalid Class String error when I try to select the external package. I'm using Windows authentication.

Any ideas on what the issue could be?

Error message

Invalid class string
------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Application.GetPackageInfos(String strFolder, String serverName, String serverUserName, String serverPassword)
   at Microsoft.SqlServer.Dts.Tasks.ExecutePackageTask.PackageBrowser.<>c__DisplayClass3_0.<EditValue>b__0(String path)
   at Microsoft.SqlServer.Dts.Tasks.ExecutePackageTask.PackageBrowser.EditValue(ITypeDescriptorContext context, IServiceProvider provider, Object value)

===================================

Invalid class string
------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetPackageInfos(String bstrPackageFolder, String bstrServerName, String bstrServerUserName, String bstrServerPassword)
   at Microsoft.SqlServer.Dts.Runtime.Application.GetPackageInfos(String strFolder, String serverName, String serverUserName, String serverPassword)

Connection properties

CodePudding user response：

Along the lines of what @larnu mentions in the comments, the Execute Package Task can run packages within the current project, or packages stored in the File System or the msdb.

To run a package in the SSISDB, I think that's going to get "interesting."

At it's core, running a package in the SSISDB is a few Stored Procedure calls.

Declare @execution_id bigint  
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Child1.dtsx'
, @execution_id=@execution_id OUTPUT
, @folder_name=N'TestDeply4'
, @project_name=N'Integration Services Project1'
, @use32bitruntime=False
, @reference_id=Null;

Select @execution_id  
DECLARE @var0 sql_variant = N'Child1.dtsx'  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Parameter1', @parameter_value=@var0  

DECLARE @var1 sql_variant = N'Child2.dtsx'  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Parameter2', @parameter_value=@var1  

DECLARE @var2 smallint = 1  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var2  

EXEC [SSISDB].[catalog].[start_execution] @execution_id

That mess would find package 'Child1.dtsx' in the project 'Integration Services Project1' which is in folder 'TestDeply4' and create an execution context. It assigns values for Parameters 1 and 2. Finally, it starts execution.

Therefore, to run an SSIS package in the SSISDB from an "external" project, you'll need to replicate those same calls in an Execute SQL Task

What's so interesting

The stored procedures that the SSISDB uses to do all the magic is CLR based and there's all sorts of impersonation that goes on. This is one of the reasons the account requesting a package to run must be a AD backed account and not a DB only account. I would encourage you to get a working POC together and deploy to an environment and see what happens. Things might work just fine. Otherwise, I can see attempts at double impersonation fouling this whole approach up.