C# 如何从 .NET 执行 SSIS 包？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/273751/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute an SSIS package from .NET?
提问by
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.
我有一个 SSIS 包，最终我也想传递参数，这些参数将来自 .NET 应用程序（VB 或 C#）所以我很好奇是否有人知道如何做到这一点，或者更好的网站提供有用的提示关于如何做到这一点。
So basically I want to execute a SSIS package from .NET passing the SSIS package parameters that it can use within it.
所以基本上我想从 .NET 执行一个 SSIS 包，传递它可以在其中使用的 SSIS 包参数。
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.
例如，SSIS 包将使用平面文件导入 SQL 数据库，但文件的路径和名称可能是从 .Net 应用程序传递的参数。
回答by Craig Schwarze
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");
}
回答by Faiz
To add to @Craig Schwarze answer,
要添加到@Craig Schwarze 的答案，
Here are some related MSDN links:
以下是一些相关的 MSDN 链接：
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 Server0\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();
}
}
}
回答by Paul Hatcher
Here's how do to it with the SSDB catalog that was introduced with SQL Server 2012...
以下是如何使用 SQL Server 2012 引入的 SSDB 目录进行操作...
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
该代码是对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/
http://domwritescode.com/2014/05/15/project-deployment-model-changes/上也有类似的文章
回答by Ayan Chakraborty
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.
因此，您可以通过另一种方式从任何语言中启动它。我认为最好的方法是，您可以创建一个批处理文件，该文件将调用您的 .dtsx 包。
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.
接下来，您可以从任何语言调用批处理文件。与 Windows 平台一样，您可以从任何地方运行批处理文件，我认为这将是最通用的方法。没有代码依赖。
Below is a blog for more details..
下面是一个博客以获取更多详细信息..
https://www.mssqltips.com/sqlservertutorial/218/command-line-tool-to-execute-ssis-packages/
https://www.mssqltips.com/sqlservertutorial/218/command-line-tool-to-execute-ssis-packages/
Happy coding.. :)
快乐编码.. :)
Thanks, Ayan
谢谢，阿扬
回答by rafayel ahmed
You can use this Function if you have some variable in the SSIS.
如果 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");
}
