Can you run an SSIS task from .net?

Question

I have scheduled sql agent task which runs an SSIS package. I want to be able to run the SSIS package from .net. Is there a way to either run the SSIS package directly or at least run the SQL agent task which would in turn run the SSIS package.

If it helps it is for a .net 3.5 web app written in C#

Thanks!

Answer 1

Yes. Look into Microsoft.SqlServer.Dts.Runtime namespace. The Package class will provide the methods to run it.

Answer 2

The options that are available to run a SSIS package are -

• Run package programmatically using SSIS Object Model. This is discussed in details in Books Online here.

An Example:

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

namespace RunFromClientAppCS
{
    class Program
    {
        static void Main(string[] args)
        {
            string pkgLocation;
            Package pkg;
            Application app;
            DTSExecResult pkgResults;

            pkgLocation = "<package path>\CalculatedColumns.dtsx";
            app = new Application();
            pkg = app.LoadPackage(pkgLocation, null);
            pkgResults = pkg.Execute();

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

• Start DTEXEC.EXE process. DTEXEC is command line utility for executing SSIS packages. See its command line options here.

• Use SQL Agent. You can configure an Agent job to run your package (either do it manually in advance if the package is static, or programmatically using SMO or using SQL stored procedures just before running the package), and then start it programmatically using SMO or sp_start_job.

• Use some other utility to start DTEXEC for you.

• Create a custom application that will run the package (either using OM as described in method #1, or using DTEXEC as in method #2). Expose it as a web service or DCOM class, call this service from your program.

• Invent your own :)

Reference: Running SSIS Package Programmatically