使用和执行SQL Server Integration Services包的方法

SQL 小号 QL Server 小号 erver Integration 我 ntegration Services) are a part of the Microsoft SQL Server database platform and a tool for building high performance data integration and workflow applications. It is also a tool for data extraction, transformation, and loading (ETL), and can be used to automate maintenance of the SQL Server databases and updates. Basically, they can be used for moving data, with no transformations, from a variety of source types to a variety of destination types, including text files and other SQL Server instances 小号 ervices）是Microsoft SQL Server数据库平台的一部分，用于构建高性能数据集成和工作流应用程序的工具。 它也是用于数据提取，转换和加载（ETL）的工具，可用于自动维护SQL Server数据库和更新。 基本上，它们可以用于从各种源类型到各种目标类型（包括文本文件和其他SQL Server实例）的数据传输，而无需进行任何转换。

These packages can be stored on the Integration Services server, in the SSIS Package Store, msdb database, and in the file system, outside the location that is part of the package store

这些程序包可以存储在Integration Services服务器上， SSIS程序包存储，msdb数据库中以及文件系统中 ，在程序包存储的一部分之外。

In this article, I will explain how to execute SSIS packages using:

在本文中，我将解释如何使用以下命令执行SSIS包：

• SQL Server Management Studio (SSMS),
  SQL Server Management Studio（SSMS），
• DTEXECUI.EXE Utility
  DTEXECUI.EXE实用程序
• DTEXEC.EXE Command Line Utility
  DTEXEC.EXE命令行实用程序
• SQL Server Agent Job
  SQL Server代理作业

使用SSMS执行SSIS包 (Executing SSIS packages using SSMS)

Execution in SSMS is can be accomplished if connection is established through an instance of the Database engine that hosts the Integration Service server (Package must be stored on the Integration Service server), or if the connection is established through the Integration Server that manages an SSIS package store (A package must be stored in the package store or in the msdb database)

如果通过承载Integration Service服务器的数据库引擎实例建立连接（包必须存储在Integration Service服务器上），或者通过管理SSIS的Integration Server建立连接，则可以完成SSMS的执行。软件包存储（软件包必须存储在软件包存储或msdb数据库中）

To execute a packages located in the Integration Services catalogs under the SSISDB node in the Object Explorer pane, right click on it and choose the Execute option. Before executing configure package execution, if needed, using settings in the Parameters, the Connection Managers and the Advanced tabs in the Execute package dialog box like shown in the image below

要执行位于“ 对象资源管理器”窗格中 SSISDB节点下的Integration Services目录中的软件包，请右键单击它，然后选择“ 执行”选项。 在执行配置程序包执行之前，如果需要，请使用“ 执行程序包”对话框中的“ 参数” ，“ 连接管理器”和“ 高级”选项卡中的设置，如下图所示。

Executing a package from the SSMS in other case, when connection is established through the Integration Server that manages the package store is also available. After locating a package under the Integration Services database type in the Object Explorer, simply right-click and choose the Run Package option as shown in the image below

在其他情况下，当通过管理软件包存储的Integration Server建立连接时，也可以从SSMS执行软件包。 在对象资源管理器中的Integration Services数据库类型下找到包之后，只需右键单击并选择“ 运行包”选项，如下图所示。

Choosing the Run Package option from the drop down list will open the Execute Package Utility window. Through several tabs on the left user can configure the Execution Options, the Reporting, the Logging options, and add configuration or command files. After configuration parameters is set, a package can be executed by clicking on the Execute button as shown in the image below

从下拉列表中选择“ 运行软件包”选项将打开“ 执行软件包实用程序”窗口。 通过左侧用户上的几个选项卡，可以配置执行选项，报告，日志记录选项 ，并添加配置或命令文件。 设置配置参数后，可以通过单击“ 执行”按钮执行程序包，如下图所示。

There is another way to start the Execute Package Utility window, using the Command Line Interface (CLI). Syntax for this utility is: C:\&>dtexecui, that will open the Execute Package Utility window

还有一种使用命令行界面（CLI）启动“ 执行软件包实用程序”窗口的方法。 该实用程序的语法是： C：\＆> dtexecui ，它将打开“ 执行软件包实用程序”窗口。

Executing a package using this method will give the results in the Package Execution Process window. All processes, warnings, errors, and validations will be presented and can be used for further analysis

使用此方法执行程序包将在“ 程序包执行过程”窗口中给出结果。 将显示所有过程，警告，错误和验证，并可用于进一步分析

Packages in the package store, the msdb database, and the file system cannot be executed in the SSMS if the connection is established through an instance of the Database engine that hosts the Integration Services server. However, it is available to import a package to a server from this locations, and then execute it

如果通过托管Integration Services服务器的数据库引擎实例建立连接，则无法在SSMS中执行程序包存储，msdb数据库和文件系统中的程序包。 但是，可以从此位置将包导入到服务器，然后执行它

Also, if a connection is established through the Integration Services service that manages the package store, packages stored in the Integration Services server cannot be executed. The package stored in the file system cannot be executed directly, but can be imported to the package store, and then executed

另外，如果通过管理软件包存储的Integration Services服务建立了连接，则无法执行存储在Integration Services服务器中的软件包。 存储在文件系统中的包不能直接执行，但可以导入到包存储中，然后执行

使用DTEXEC.exe命令行实用工具执行SSIS包 (Execute SSIS packages using DTEXEC.exe Command Line Utility)

There is a way to execute all packages from SSMS, no matter if they are stored on the Integration Services server, the package store, the msdb database, or in the file system, and no matter if a connection is established through the Integration Services server or the Database engine that hosts the Integration Services server

有一种方法可以从SSMS执行所有程序包，无论它们是存储在Integration Services服务器， 程序包存储 ， msdb数据库还是文件系统中，也无论是否通过Integration Services服务器建立连接或承载Integration Services服务器的数据库引擎

Using the dtexec command in cmd avoids connection and storing limitations. The Command Line Interface with all switches available for executing is shown in the image below

在cmd中使用dtexec命令可避免连接和存储限制。 下图显示了带有可用于执行的所有开关的命令行界面

For example, syntax for the package named “Package1” located on the “Test” SQL Server can be:

例如，位于“测试” SQL Server上的名为“ Package1”的程序包的语法可以是：

C:\dtexec /SQL “\Package1” /SERVER “Test”

C：\ dtexec / SQL“ \ Package1” / SERVER“测试”

Another example for the package named “Package1” located the file system

名为“ Package1”的程序包的另一个示例位于文件系统中

C:\dtexec /F “C:\User\Packages\Package1.dtsx”

C：\ dtexec / F“ C：\ User \ Packages \ Package1.dtsx”

使用SQL Server代理作业执行SSIS包 (Executing SSIS packages using SQL Server Agent Job)

Execution can be scheduled using the SQL Server Agent Job. Before creating a new job, and scheduling, the SQL Agent Job service must be started

可以使用SQL Server代理作业计划执行。 在创建新作业和计划之前，必须启动SQL Agent Job服务

In the Object Explorer right click on the SQL Server Agent, under the database engine node, select New, and choose Job, from the drop down list, and the New Job window will open. In the General tab, the Name field is mandatory. To schedule a job, at least one step must be created. Steps can be created from the Steps tab clicking the New button, a window will open as shown in the image below

在对象资源管理器中，右键单击SQL Server Agent ，在数据库引擎节点下，选择New ，然后从下拉列表中选择Job ，然后将打开New Job窗口。 在常规选项卡中， 名称字段是必填字段。 要安排作业，必须至少创建一个步骤。 可以从“ 步骤”选项卡中单击“ 新建”按钮来创建步骤 ，如下图所示，将打开一个窗口

The Step Name field is mandatory. From the Type drop down list, the SQL Server Integration Services Package option needs to be selected, and the SQL Server Agent Service Account from the Run as drop down list. As a package source the File system option needs to be selected, to locate a package on a local drive, and import it as step for the task. Note that more options for a package source like SQL Server, Package Store, and SSIS catalog are also available

步骤名称字段是必填项。 从“ 类型”下拉列表中，需要选择“ SQL Server集成服务包”选项，并从“ 运行方式”下拉列表中选择“ SQL Server代理服务帐户 ” 。 作为包源，需要选择“ 文件系统”选项，以在本地驱动器上找到包，然后将其导入作为该任务的步骤。 请注意，还提供了包源（如SQL Server，包存储和SSIS目录）的更多选项。

A created job can be started manually whenever the user wants, or it can be scheduled to run automatically. Job scheduling can be set under the Schedules tab

创建的作业可以在用户需要时手动启动，也可以安排为自动运行。 可以在“ 计划”选项卡下设置作业计划

The user can set scheduling parameters by clicking the New (setting up new parameters), or Pick (running job along with already defined tasks) as shown in the image below

用户可以通过单击“ 新建” （设置新参数）或“ 选择” （正在运行的作业以及已定义的任务）来设置计划参数，如下图所示。

These are various ways to execute packages using SSMS. Also, they can be executed using SQL Business Intelligence Development Studio, using built-in stored procedures, Managed API- using types and members in the Microsoft.SqlServer.Management.IntegrationServices namespace, and Managed API – using types and members in the Microsoft.SqlServer.Dts.Runtime namespace. These will be covered in another article

这些是使用SSMS执行程序包的各种方法。 此外，可以使用SQL Business Intelligence Development Studio，内置存储过程，使用Microsoft.SqlServer.Management.IntegrationServices命名空间中的类型和成员的Managed API和使用Microsoft中的类型和成员的Managed API来执行它们。 SqlServer.Dts.Runtime命名空间。 这些将在另一篇文章中介绍

有用的资源 (Useful resources)

• Different ways to Execute SSIS Packages执行SSIS包的不同方法
• Execution of Projects and Packages 项目和包的执行

翻译自: https://www.sqlshack.com/ways-use-execute-sql-server-integration-services-packages/