Running Integration services SSIS on .net core 3.0

Question

We are in the process of porting one WPF application to .net core and we sort of got stuck on SSIS portion. Previously we used Microsoft.SqlServer.Management.Sdk.Sfc and Microsoft.SqlServer.Smo to run SSIS using this code:

    public void SSISUpload()
    {
        string targetServerName = "server";
        string folderName = "Project1Folder";
        string projectName = "Integration Services Project";
        string packageName = "SSISPackage/Package.dtsx";

        // Create a connection to the server
        string sqlConnectionString = "Data Source=" + targetServerName +
            ";Initial Catalog=master;Integrated Security=SSPI;";
        SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);

        IntegrationServices integrationServices = new IntegrationServices(sqlConnection);
        Catalog catalog = integrationServices.Catalogs["SSISDB"];
        CatalogFolder folder = catalog.Folders[folderName];
        ProjectInfo project = folder.Projects[projectName];
        PackageInfo package = project.Packages[packageName];

        // Run the package
        package.Execute(false, null);
    }

However, the above references tie to .NET Framework and do not seem to have bindings for .net core or standard. We tried using Microsoft.SqlServer.SqlManagementObjects, which does have standard2.0 bindings, but that does not really translate 1-1 (classes are not there) and there does not seem to be any info online on how to achieve running SSIS from .net core/standard. Anyone managed to do this?

Answer 1

You can use different approaches to execute SSIS packages from C# .net core:

Using Transact-SQL command

Instead of using Microsoft.SqlServer.SqlManagementObjects you can simply use an SQLCommand to execute SSIS packages, as example:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
      @project_name=N'Integration Services Project1',
    @use32bitruntime=False,
      @reference_id=Null
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

You can refer to the following links for more information:

• Run an SSIS package from Visual Studio Code with Transact-SQL
• Deploy an SSIS project from Visual Studio Code with Transact-SQL

Using DTEXEC utility

Another option is to use Process.Start method to execute DTEXEC application, which is installed with SQL Server.As Example:

Process p = new Process();

// Redirect the output stream of the child process.

p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.FileName = @"C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTExec.exe";
p.StartInfo.Arguments = "/ISServer \"\SSISDB\Project1Folder\Integration Services Project1\Package.dtsx\" /Server \"localhost\"";
p.Start();
Debug.WriteLine(p.StandardOutput.ReadToEnd());
p.WaitForExit();

For more information, you can refer to the following links:

• Run an SSIS package from the command prompt with DTExec.exe
• dtexec Utility
• Running SSIS packages outside the Developer tools using DTEXEC.exe without installing the Integration services
• Kicking SSIS package off using C# program