SSIS : Executing local dtsx package without using Microsoft.SqlServer.Dts.Runtime in .NET 6 with SQL Server 2019

Question

I am working on upgrading C# web service from .net 4.5 to .NET 6.

There is no / could not find replacement for Microsoft.SqlServer.Dts.Runtime in .net core or .NET 6. https://github.com/MicrosoftDocs/sql-docs/issues/3747. I cant directly import it in NET 6 application as application will not be platform independent then. Please let me know if there are any other ways to still use "Microsoft.SqlServer.Dts.Runtime" in NET6.

Existing code loads and runs the local .dtsx package from bin folder of application programmatically using

https://learn.microsoft.com/en-us/sql/integration-services/run-manage-packages-programmatically/loading-and-running-a-local-package-programmatically?redirectedfrom=MSDN&view=sql-server-ver16

Part of the code is

  string pkgLocation;  
  Package pkg;  
  Application app;  
  DTSExecResult pkgResults;  
  
  //package present in bin folder
  pkgLocation =  Path.Combine(assemblyExecutingPath, @"bin\Packages\Pkg.dtsx");
  
  var eventListener = new SsisEventListener();

  //Load local package
  app = new Application();  
  pkg = app.LoadPackage(pkgLocation, eventListener );
 
  pkg.Variables["USER::SourceConnectionString"].Value = connectionstring  //Connectionstring with SSIP and sqlni provider

  //Execute 
  pkgResults = pkg.Execute(null,Variable, pkg.eventListener,null,null );

As Microsoft.SqlServer.Dts.Runtime is not available in .NET 6, I am looking for other options to execute LOCAL .dtsx packages. Looking into T-SQL option I found this:

Executing a dtsx file from a stored procedure

where following options are mentioned

1. Run the package by calling dtexec from the stored procedure query => it runs in SQL Server, so it will not be useful for executing local .dtsx packages? As that requires using xp_cmdshell, which is not the most secure method?

2. Write a CLR procedure that uses the SSIS object model to run the package

   Query/Problem => useful for executing local dtsx packages? SSIS object model not compatible with .NET 6

3. Create a SQL Server Agent job that runs the package and run the job using the sp_start_job stored procedure

   Query/Problem => need more information.

If there is any other way to execute local .dtsx packages, please let me know.

Thanks.

Answer 1

As of 2023-05-23, SSIS works with the .NET Framework and that's it. Core, Standard, Diet or any other variation they come up with in the future don't support it.

Your options for running an SSIS Package are

• .NET Framework libraries
• CLR stored procedures that run SSIS packages in the SSISDB, which under the covers use the .NET Framework Libraries
• DTEXEC.exe, 32 or 64 bit variant, which under the covers use .NET Framework Libraries