如何从.NET 执行 SSIS 包？

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

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

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/

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