Run SSIS in .Net 5 Application

Question

I want to create .Net 5 application that can run SSIS package.

Here is my code to execute the package.

Application app = new Application();
Package package = app.LoadPackage(@"C:\Package1.dtsx", null);

for (int i = 0; i < package.Connections.Count; i++)
{
    Connections connections = package.Connections;

    //This is the line that causes error
    ConnectionManager connectionManager = connections[0];

    Console.WriteLine(connectionManager.Name);
}

DTSExecResult results = package.Execute();

It compiles without error, but when executed, at line that get the connection, it raise an error that said

Could not load file or assembly 'Microsoft.SqlServer.Diagnostics.STrace, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The system cannot find the file specified.

I can't even find dll for Microsoft.SqlServer.Diagnostics.STrace. I think its because I use .Net Core and the SSIS library only support .Net Framework, but running SSIS is required in the project so I tried to find a way to execute it even though the resulting .Net Core application can only be used in windows environment. Any one knows how to do this?

Answer 1

As of current version of SSIS, SQL Server 2019, it is a .NET Framework based solution. Script Tasks target 4.5 as a default so take that as an indicator of how far from current Framework, SSIS is based. I don't believe you're going to be able to use the SSIS object model to control package execution.

The other thing to be aware of, even if you manage to get this to work you're likely going to run into legal issues. Running an SSIS package outside of Visual Studio requires a SQL Server license installed on the machine that is running an SSIS package. Period, full stop, do not pass go, do not collect $200. So if your application does this cool stuff as well as runs SSIS packages, the app consumers need to be ready for a licensing fee of 10-20k per core on the machine that runs it, minimum of 4 cores.

Otherwise, you're going to run into an error like

To run a SSIS package outside of SQL Server Data Tools you must install Standard edition of Integration Services or higher

or the error specifies a component isn't installed.

So, assuming you have licensing squared away and no one else has a better idea of how to make the .Net Core manipulate .Net Framework code - how else can you run a package?

DTEXEC

When you install "SQL Server Integration Services" Service, which is on the SQL Server install media, there is an option for the SSIS Service. This gets you through the licensing check and also installs dtexec.exe in the 32 and 64 bit application paths. Running a package deployment model package becomes dtexec /f path/to/my/package.dtsx

Knowing that, it looks like Core supports System.Diagnostics.Process so you're looking at a call like Process.Start("dtexec.exe"); after setting all the parameters https://stackoverflow.com/a/181857

SSISDB

Create your packages using the Project Deployment Model and deploy to a licensed SQL Server machine and the hard stuff goes away. All your application has to worry about is making a connection to SQL Server and then asking SQL Server to run the package.