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/