sql server 部署_将程序包部署到SQL Server集成服务目录（SSISDB）

sql server 部署

Starting with SQL Server 2012, Integration Services (SSIS) packages can now be deployed to a single source for managing execution in multiple environments. The SSIS Catalog is a single database container for all deployed packages. The configuration files are replaced with Environments. Deployed versions are tracked historically and a package can be reverted to a previous deployment. On top of these features, internal reports with a dashboard help when debugging errors or examining performance over time.

从SQL Server 2012开始，现在可以将Integration Services（SSIS）程序包部署到单个源，以管理多个环境中的执行。 SSIS目录是所有已部署程序包的单个数据库容器。 配置文件将替换为环境。 历史上会跟踪已部署的版本，并且可以将包还原到以前的部署。 除了这些功能之外，带有仪表板的内部报告还有助于调试错误或检查性能。

To use this feature, the SSIS Catalog must be created. The System Administrator of the SQL Server instance needs to create the Catalog or a user with elevated permissions for creating databases. Figure 1 shows the menu after right-clicking on the Integration Services Catalog folder in SQL Server Management Studio (SSMS).

要使用此功能，必须创建SSIS目录。 SQL Server实例的系统管理员需要创建目录或具有提升权限的用户来创建数据库。 图1显示了右键单击SQL Server Management Studio（SSMS）中的Integration Services Catalog文件夹后的菜单。

The Create Catalog… meu option will launch the Create Catalog window. Some configuration takes place during its creation. CLR Integration will need to be enabled on this instance. On SQL Server Startup, the automatic execution of SSIS stored procedure(s) can be enabled or not. The most important part is to provide a strong password for the management of this new database container.

“创建目录...”菜单选项将启动“创建目录”窗口。 在创建过程中会进行一些配置。 需要在此实例上启用CLR集成。 在SQL Server启动时，可以启用或不启用SSIS存储过程的自动执行。 最重要的部分是为此新数据库容器的管理提供一个强密码。

When the configuration is complete, a new database is created on this instance: SSISDB. The database needs the same maintenance as any other production database on this system. Maintenance items include backups, index rebuild/reorganize and update statistics. The recovery mode of the database inherits the properties from the Model database just like all new databases. Simple recovery mode is adequate for SSISDB because of the infrequent deployments.

配置完成后，将在以下实例上创建一个新数据库：SSISDB。 该数据库需要与该系统上任何其他生产数据库相同的维护。 维护项目包括备份，索引重建/重组和更新统计信息。 与所有新数据库一样，数据库的恢复模式从Model数据库继承属性。 由于部署很少，因此简单恢复模式足以满足SSISDB的需求。

A user does not need to be a SysAdmin to deploy projects (and packages) to the Catalog. There is a Database Role named ssis_admin in the SSISDB database. This role contains the permissions to deploy projects from Visual Studio. A developer’s Active Directory (AD) account or an AD group can be added to this role.

用户不需要是SysAdmin即可将项目（和程序包）部署到目录中。 SSISDB数据库中有一个名为ssis_admin的数据库角色。 该角色包含从Visual Studio部署项目的权限。 可以将开发人员的Active Directory（AD）帐户或AD组添加到该角色。

Deploying SSIS projects, along with the project’s package(s), was added in SQL Server 2012 as well as the SSIS Catalog. This organization of packages in a SSIS Project enables objects, properties and values to be shared among the packages in a project. Figure 5 shows the project.param file associated with a SSIS Project.

SQL Server 2012和SSIS目录中添加了部署SSIS项目以及该项目的程序包。 SSIS项目中软件包的这种组织结构使对象，属性和值可以在项目中的软件包之间共享。 图5显示了与SSIS项目关联的project.param文件。

This project has multiple packages. The Source Database and Source Server is shared with packages like DimProduct and DimCategory. The connection is created under the Connection Manager in the Project’s solution as seen in Figure 6. Not only can the packages can share parameter values, it also shares these database connections. This example contains staging, source and destination databases. The prefix (project) is added to the Package Connection Managers’ connections as seen in Figure 6 under the objects of the package.

该项目有多个软件包。 源数据库和源服务器与DimProduct和DimCategory等软件包共享。 如图6所示，该连接是在Project解决方案的Connection Manager下创建的。这些包不仅可以共享参数值，而且还可以共享这些数据库连接。 此示例包含登台数据库，源数据库和目标数据库。 前缀（项目）被添加到包连接管理器的连接中，如图6所示，在包对象下。

In this environment, the development team uses a separate server as the testing team and production system. The use of Project Parameter enables a single change to effect multiple packages in the project. To deploy the project to the SSIS Catalog, right-click the project in the solution explorer of Visual Studio.

在这种环境下，开发团队使用单独的服务器作为测试团队和生产系统。 使用项目参数可以使单个更改影响项目中的多个程序包。 要将项目部署到SSIS目录中，请在Visual Studio的解决方案资源管理器中右键单击该项目。

The Integration Services Deployment Wizard will show a welcome screen. You can disable this screen for future deployments. The Select Source will default to the project selected while in Visual Studio. The wizard first brings you to the Select Destination page, but you can use the back button to go to the Select Source page. The Source Selection can be changed, but usually the deployment wizard is launched from the project being deployed. Figure 8 shows the Select Destination screen where the Server Name and Path in the SSIS Catalog are selected.

Integration Services部署向导将显示一个欢迎屏幕。 您可以禁用此屏幕以供将来部署。 选择源将默认为在Visual Studio中选择的项目。 该向导首先将您带到“选择目标”页面，但是您可以使用“后退”按钮转到“选择源”页面。 可以更改“源选择”，但是通常会从正在部署的项目中启动部署向导。 图8显示了“选择目标”屏幕，在其中选择了SSIS目录中的“服务器名称”和“路径”。

The Server name will be the instance the Catalog was created. The Path can be an existing one or a New Path can be created. The same path can be used for different projects. All project packages will be together under the project in the Path.

服务器名称将是创建目录的实例。 路径可以是现有路径，也可以创建新路径。 相同的路径可用于不同的项目。 所有项目包都将放在路径中的项目下。

The Review screen gives the options to view the selected source and destination. Once the Deploy button is selected on the Review screen, the Results screen will show the success list or an indication of what failed during deployment.

“查看”屏幕提供了用于查看所选源和目标的选项。 在“查看”屏幕上选择“部署”按钮后，“结果”屏幕将显示成功列表或在部署过程中失败的指示。

A package can be launched in several ways. In SQL Server Management Server (SSMS), the Integration Services Catalog folder can be expanded to see the Paths created in the Catalog. Figure 10 shows the menu from right-clicking the package DimCategory in the SSIS Catalog.

一个包可以通过几种方式启动。 在SQL Server Management Server（SSMS）中，可以将Integration Services Catalog文件夹展开以查看在Catalog中创建的路径。 图10显示了在SSIS目录中右键单击软件包DimCategory的菜单。

After selecting Execute… from the menu, the execution prompts for Parameters, Connection Managers and Advanced options. Figure 11 show the Parameters that can be changed before the DimCategory package is executed. This is where we can change the server or database names for the Project Parameters before the package is executed.

从菜单中选择“执行...”后，执行提示输入“参数”，“连接管理器”和“高级”选项。 图11显示了在执行DimCategory包之前可以更改的参数。 在执行包之前，我们可以在这里更改项目参数的服务器或数据库名称。

One of the cool features of the SSIS Catalog are free reports. Figure 12 shows the execution report that can be displayed after launching the package. There is a prompt to view or not view the report. There are two additional links to drill down into Messages and Performance. Individual Tasks can be drilled into to show messages from different steps of the package.

SSIS目录的一项很酷的功能是免费报告。 图12显示了启动程序包后可以显示的执行报告。 提示您查看或不查看报告。 还有两个附加链接可深入研究“消息和性能”。 可以深入研究各个任务，以显示来自软件包不同步骤的消息。

If there is an error, the messages are the best place to start to debug a package. To see the performance of this package over time, click the View Performance drill down report.

如果有错误，则消息是开始调试软件包的最佳位置。 要查看此程序包随时间的性能，请单击“查看性能”明细报表。

There is a main Dashboard that can be used to see an overview of all packages running from the Catalog. This dashboard is launched from SSMS by right-clicking on the SSISDB folder under the Integration Services Catalog. Figure 13 shows the path to the report.

有一个主仪表板，可用于查看从目录运行的所有软件包的概述。 通过右键单击Integration Services目录下的SSISDB文件夹，从SSMS启动此仪表板。 图13显示了报告的路径。

Figure 14 shows for today one package has executed successfully and another has failed.

今天的图14显示了一个程序包已成功执行而另一个程序包已失败。

By clicking on the Failed number, the next report gives the ability to drill into the Messages returned from the success and fail steps of the package like in Figure 15

通过单击Failed号，下一个报告使您能够深入研究从包成功和失败步骤返回的消息，如图15所示。

As you can see, the SSIS Catalog has a wealth of information and management for SSIS projects and packages. The reports and drill down capabilities help with debugging problems before opening the package in Visual Studio. Performance reports can give an idea if a package is taking longer to run in the last execution than previous executions. History can be configured and is explained more in the side note at the end of this article. The Catalog is a great place to store and manage SSIS package deployment and execution.

如您所见，SSIS目录为SSIS项目和程序包提供了大量信息和管理。 报告和追溯功能有助于在Visual Studio中打开程序包之前调试问题。 性能报告可以告诉您一个包在上一次执行中花费的时间是否比以前的执行时间更长。 可以配置历史记录，并在本文末尾的旁注中对其进行了详细说明。 该目录是存储和管理SSIS包部署和执行的好地方。

有用的链接 (Useful links)

• Catalog Reports 目录报告
• Project and Package Parameters 项目和包装参数
• Project Connection Managers 项目连接经理

边注 (Side Note)

Other configuration options are available by going to the properties of the Integration Services Catalog from SSMS. Clean Logs Periodically can be set to true or false. This enables or disables a SQL Server Agent Job that is run on a scheduled time. Retention Period (days) indicates to the job how long to keep history of the execution of the packages.

通过从SSMS转到Integration Services目录的属性，可以使用其他配置选项。 定期清除日志可以设置为true或false。 这将启用或禁用在计划的时间运行SQL Server代理作业。 保留期（天）向工作指示将包执行历史记录保留多长时间。

The descriptions of the Server-wide Default Logging Level options can be seen at the following link: Enable Logging for Package Execution on the SSIS Server. The Maximum Number of Version per Project indicates how many deployments will be saved to the catalog of a project. Purging these versions can be controlled by the property Periodically Remove Old Versions.

可以在以下链接中找到“服务器范围的默认日志记录级别”选项的描述： 在SSIS服务器上为程序包执行启用日志记录 。 每个项目的最大版本数指示将有多少个部署保存到项目目录中。 可以通过属性“定期删除旧版本”来控制清除这些版本。

翻译自: https://www.sqlshack.com/deploying-packages-to-sql-server-integration-services-catalog-ssisdb/

sql server 部署