execute ssis package in MVC

Question

Afternoon,

Is it possible to execute a SSIS package using MVC? What I am creating is a web application and it would have a button, once clicked the SSIS package runs.

The SSIS project is already set up and deployed on the MSSQL server.

Answer 1

SQL Server 2012+ offers a fantastic mechanism for managing packages and their execution via the Integration Services catalog, SSISDB.

The following code provides an example of running the package (Package2.dtsx) from the MyProjectName SSIS project living under the Demo folder with an IntensityLevel package parameter turned up to 11.

DECLARE @execution_id bigint;

EXEC SSISDB.catalog.create_execution
    @package_name = N'Package2.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'Demo'
,   @project_name = N'MyProjectName'
,   @use32bitruntime = False
,   @reference_id = NULL;

DECLARE @var0 int = 11;

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 30
,   @parameter_name = N'IntensityLevel'
,   @parameter_value = @var0;

DECLARE @var1 smallint = 1;

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var1;

EXEC SSISDB.catalog.start_execution
    @execution_id;

The easy way to get a sample of how the above SQL should be built out is to open SQL Server Management Studio (SSMS) and configure a run of the SSIS package. Navigate to the Integration Services Catalog and find the package you want to run. Right click and select Execute...

The Configuration menu opens up and find the parameter(s) you want to specify. Provide a sample value but DO NOT CLICK OK. Instead, click that Script button and specify script to new window (or clipboard)

Now you have the exact commands that SSMS would have issued to run your package. Take that code, use your parameterization method of choice for having your MVC program stubbing in the correct runtime value for your parameter and then wrap all of that TSQL up in a simple database call (ole, ado, odbc it won't matter)

Answer 2

You could just create a stored procedure which you can call from your MVC app with SQLClient or so.. In this stored procedure you can then launch the SSIS package.(see below link for more detailed description on how to do this)

https://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/

Answer 3

I created a stored procedure which runs the SSIS package. Within MVC it calls the stored procedure -

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Index()
{
    //code that updates DB

    #region Run Stored Procedure
    //connect to the SQL server
    var connection = new SqlConnection(_configuration.GetConnectionString("DatabaseConnection"));
    //command that runs procedure on the SQL server
    var command = new SqlCommand("RebuildSelection", connection)
    {
        CommandType = CommandType.StoredProcedure,
        CommandText = "RebuildSelection"
    };
    //get text from stored procedure to show success/error messages
    SqlParameter text = new SqlParameter("@Text", SqlDbType.NVarChar, 1000)
    {
        //output as its displayed to the user
        Direction = ParameterDirection.Output
    };
    //add the params
    command.Parameters.Add(text);
    connection.Open();
    //run query
    command.ExecuteNonQuery();
    //used to return success/error messages to user
    ViewData["Message"] = text.Value;
    connection.Close();
    #endregion

    return View();
}

This link was a big help for creating the procedure.

This one helped in returning message to the user.

Answer 4

C# can be used to execute a package deployed to SSISDB as follows. The initial connection created in the SqlConnection object will be the server where the package is deployed to. In the example below, the ObjectType of 30 is used for a package parameter. This can be changed to 20 for a parameter at the project level.

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;

        string folder = "Folder of Package";
        string project = "Project Of Package";
        string packageName = "PackageName.dtsx";
        string packageParameter = "ParameterValue";

        // server where package is deployed
        SqlConnection connString = new SqlConnection(@"Data Source=ServerWherePackageDeployed;Initial Catalog=SSISDB;Integrated Security=SSPI;");
        IntegrationServices ssisConnString = new IntegrationServices(connString);

        //create PackageInfo object for package to execute
        Microsoft.SqlServer.Management.IntegrationServices.PackageInfo package = ssisConnString.Catalogs["SSISDB"].Folders[folder].Projects[project].Packages[packageName];

        Collection <Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet> parameterList = new Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet>();

        //set logging level to basic
        parameterList.Add(new Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 1 });

        parameterList.Add(new Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "ParameterName", ParameterValue = packageParameter });

        package.Execute(false, null, parameterList);