How to execute an SSIS package from .NET?

Question

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.

Solution 1

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");
    }

Solution 2

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/

Solution 3

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();
    }
  }
}

Solution 4

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

View more solutions

Comments

[Ian Campbell]

Thanks @Craig! However, Dts is now deprecated as of SQL Server 2008, how can you do this without the Dts package?

[Spikeh]

@IanCampbell I assume you're referring to Microsoft.SqlServer.Dts.Runtime? Dts is just the legacy name for SSIS - it's just the namespace declaration. The code above is supported going forward.

[Ian Campbell]

@Spikeh -- what's confusing me is that Microsoft says that Dts is deprecated and "has been replaced by SQL Server Integration Services": technet.microsoft.com/en-us/library/…

[Spikeh]

@IanCampbell Yes, DTS is depreciated (in fact, I don't think you can even use DTS with the latest versions of SQL Server - not that I've tried to find it!). However, the .Net namespace containing some of the SSIS components still contains the Dts word. I assure you it's the current version and is valid.

[Ian Campbell]

Ok, thanks @Spikeh! Of note, when I recently implemented similar code to load an SSIS package with Dts, I had to manually obtain the Microsoft.SqlServer.ManagedDTS.dll file from the "GAC", in the C:\Windows\assembly folder, to compile such code.

[Spikeh]

Yes, so did I - I was doing the same yesterday! I'm using VS2012 and .Net 4 (for the SSIS package) / 4.5 (for my unit tests). I had to get the assembly from C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServ‌​er.ManagedDTS\v4.0_1‌​1.0.0.0__89845dcd808‌​0cc91 as it didn't exist in any of the other assembly folders, or in the SQL folders.

[Faiz]

Some links to MSDN: 1) Local package (same machine): msdn.microsoft.com/en-us/library/ms136090.aspx . 2) Remote package (stored on a machine other than the one where the program is running), using SQL agent jobs: msdn.microsoft.com/en-us/library/ms403355.aspx

[GKS]

@IanCampbell - There's a new API in SQL Server 2012, see below for example code

[Admin]

Where is the microsoft.sqlserver.management.integrationservices.dll located? I have SQL2014 installed and cannot find it doing a windows search.

[Admin]

Apparently it's only in the GAC: Microsoft.SqlServer.Management.IntegrationServices.dll assembly location

[Manish Jain]

Can I use above code with package deployment? I couldn't find any method.

[Alicia]

There seems to be a related and updated article on the official documentation site: docs.microsoft.com/en-us/sql/integration-services/…