How to execute an SSIS package from .NET?

I have a SSIS package that eventually I would like to pass parameters too, these parameters will come from a .NET application (VB or C#) so I was curious if anyone knows of how to do this, or better yet a website with helpful hints on how to do it.

So basically I want to execute a SSIS package from .NET passing the SSIS package parameters that it can use within it.

For instance, the SSIS package will use flat file importing into a SQL db however the Path and name of the file could be the parameter that is passed from the .Net application.

Here is how to set variables in the package from code -

using Microsoft.SqlServer.Dts.Runtime;

private void Execute_Package()
    {           
        string pkgLocation = @"c:\test.dtsx";

        Package pkg;
        Application app;
        DTSExecResult pkgResults;
        Variables vars;

        app = new Application();
        pkg = app.LoadPackage(pkgLocation, null);

        vars = pkg.Variables;
        vars["A_Variable"].Value = "Some value";               
       
        pkgResults = pkg.Execute(null, vars, null, null, null);

        if (pkgResults == DTSExecResult.Success)
            Console.WriteLine("Package ran successfully");
        else
            Console.WriteLine("Package failed");
    }

Here's how do to it with the SSDB catalog that was introduced with SQL Server 2012...

using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;

using Microsoft.SqlServer.Management.IntegrationServices;

public List<string> ExecutePackage(string folder, string project, string package)
{
    // Connection to the database server where the packages are located
    SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=master;Integrated Security=SSPI;");

    // SSIS server object with connection
    IntegrationServices ssisServer = new IntegrationServices(ssisConnection);

    // The reference to the package which you want to execute
    PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders[folder].Projects[project].Packages[package];

    // Add a parameter collection for 'system' parameters (ObjectType = 50), package parameters (ObjectType = 30) and project parameters (ObjectType = 20)
    Collection<PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<PackageInfo.ExecutionValueParameterSet>();

    // Add execution parameter (value) to override the default asynchronized execution. If you leave this out the package is executed asynchronized
    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });

    // Add execution parameter (value) to override the default logging level (0=None, 1=Basic, 2=Performance, 3=Verbose)
    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 3 });

    // Add a project parameter (value) to fill a project parameter
    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 20, ParameterName = "MyProjectParameter", ParameterValue = "some value" });

    // Add a project package (value) to fill a package parameter
    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "MyPackageParameter", ParameterValue = "some value" });

    // Get the identifier of the execution to get the log
    long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

    // Loop through the log and do something with it like adding to a list
    var messages = new List<string>();
    foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
    {
        messages.Add(message.MessageType + ": " + message.Message);
    }

    return messages;
}

The code is a slight adaptation of http://social.technet.microsoft.com/wiki/contents/articles/21978.execute-ssis-2012-package-with-parameters-via-net.aspx?CommentPosted=true#commentmessage

There is also a similar article at http://domwritescode.com/2014/05/15/project-deployment-model-changes/

To add to @Craig Schwarze answer,

Here are some related MSDN links:

Loading and Running a Local Package Programmatically:

Loading and Running a Remote Package Programmatically

Capturing Events from a Running Package:

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppWithEventsCS
{
  class MyEventListener : DefaultEvents
  {
    public override bool OnError(DtsObject source, int errorCode, string subComponent, 
      string description, string helpFile, int helpContext, string idofInterfaceWithError)
    {
      // Add application-specific diagnostics here.
      Console.WriteLine("Error in {0}/{1} : {2}", source, subComponent, description);
      return false;
    }
  }
  class Program
  {
    static void Main(string[] args)
    {
      string pkgLocation;
      Package pkg;
      Application app;
      DTSExecResult pkgResults;

      MyEventListener eventListener = new MyEventListener();

      pkgLocation =
        @"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +
        @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
      app = new Application();
      pkg = app.LoadPackage(pkgLocation, eventListener);
      pkgResults = pkg.Execute(null, null, eventListener, null, null);

      Console.WriteLine(pkgResults.ToString());
      Console.ReadKey();
    }
  }
}

So there is another way you can actually fire it from any language. The best way I think, you can just create a batch file which will call your .dtsx package.

Next you call the batch file from any language. As in windows platform, you can run batch file from anywhere, I think this will be the most generic approach for your purpose. No code dependencies.

Below is a blog for more details..

https://www.mssqltips.com/sqlservertutorial/218/command-line-tool-to-execute-ssis-packages/

Happy coding.. :)

Thanks, Ayan

You can use this Function if you have some variable in the SSIS.

    Package pkg;

    Microsoft.SqlServer.Dts.Runtime.Application app;
    DTSExecResult pkgResults;
    Variables vars;

    app = new Microsoft.SqlServer.Dts.Runtime.Application();
    pkg = app.LoadPackage(" Location of your SSIS package", null);

    vars = pkg.Variables;

    // your variables
    vars["somevariable1"].Value = "yourvariable1";
    vars["somevariable2"].Value = "yourvariable2";

    pkgResults = pkg.Execute(null, vars, null, null, null);

    if (pkgResults == DTSExecResult.Success)
    {
        Console.WriteLine("Package ran successfully");
    }
    else
    {
        
        Console.WriteLine("Package failed");
    }