ssis 创建ssisdb_SSIS目录数据库（SSISDB）简介

ssis 创建ssisdb

In this article, I am going to explain in detail the SSIS catalog that can be used to deploy SQL Server Integration Services (SSIS) projects. Using this catalog, developers and database administrators can easily deploy and manage their integration services projects after deployment. The SSIS Catalog database was introduced in SQL Server 2012 and prior to that users had the following three options to deploy their SSIS packages:

在本文中，我将详细解释可用于部署SQL Server Integration Services（SSIS）项目的SSIS目录。 使用此目录，开发人员和数据库管理员可以在部署后轻松部署和管理其集成服务项目。 SSIS目录数据库是在SQL Server 2012中引入的，在此之前，用户具有以下三个选项来部署其SSIS包：

• Users could deploy the packages to a directory on the file system
  用户可以将软件包部署到文件系统上的目录中
• Users could deploy the packages to the package store which ultimately stores the packages on the file system but inside the SQL Server directory instead
  用户可以将软件包部署到软件包存储中，该存储最终将软件包存储在文件系统上，但存储在SQL Server目录中
• Finally, users could deploy the packages to the SQL Server instance where the packages were stored within the MSDB database in the “sysssispackages” table
  最后，用户可以将程序包部署到SQL Server实例，在该实例中，程序包存储在MSDB数据库中的“ sysssispackages ”表中

创建SSIS目录 (Creating the SSIS Catalog)

In order to create the SSIS catalog database, you need to have SQL Server already up and running on the machine, to begin with. This catalog database is just another simple database object which helps us store the SSIS packages in a more organized way.

为了创建SSIS目录数据库，首先需要在计算机上启动并运行SQL Server。 该目录数据库只是另一个简单的数据库对象，可帮助我们以更有条理的方式存储SSIS包。

To create this catalog database, right-click on the “Integration Services Catalog” option and select “Create Catalog…”. In the Create Catalog dialog box that opens up, enable the checkbox for “Enable CLR Integration” and provide a password for the catalog database. You will usually not require this password to use the SSIS objects within the catalog database.

要创建此目录数据库，请右键单击“ Integration Services目录 ”选项，然后选择“ 创建目录... ”。 在打开的“创建目录”对话框中，启用“ 启用CLR集成 ”复选框，并提供目录数据库的密码。 通常，您不需要此密码即可使用目录数据库中的SSIS对象。

Figure 1 – Create the Catalog Database using SQL Server Management Studio

图1 –使用SQL Server Management Studio创建目录数据库

I have already enabled the catalog database in my machine, so I will not be able to share the screenprints at the moment. However, once you are done with setting the password, hit OK and that will be it. The new catalog database will be created, and you can see it in the following screenshot.

我已经在我的机器上启用了目录数据库，所以目前我将无法共享屏幕打印。 但是，设置完密码后，请单击“ 确定”即可 。 将创建新的目录数据库，您可以在以下屏幕截图中看到它。

Figure 2 – SSISDB created under the Integration Services Catalog

图2 –在Integration Services目录下创建的SSISDB

Integration Services目录属性 (Integration Services Catalog Properties)

Now that the catalog database has been created, let us go ahead and understand the properties of the database. By default, the following configuration will be set for the catalog database, but it can be altered based on the requirements.

现在已经创建了目录数据库，让我们继续了解数据库的属性。 默认情况下，将为目录数据库设置以下配置，但是可以根据要求进行更改。

Right-click on the SSISDB folder and select Properties. The properties dialog box will open up as follows.

右键单击SSISDB文件夹，然后选择属性 。 将打开属性对话框，如下所示。

Figure 3 – SSISDB Database properties

图3 – SSISDB数据库属性

The main properties that need to be taken care of by the database administrators are as follows:

数据库管理员需要照顾的主要属性如下：

• Clean Logs Periodically – This is a Boolean property, which if set to TRUE will clean the execution logs of the packages based on the Retention Period
  定期清除日志 –这是一个布尔属性，如果将其设置为TRUE，则将根据保留期限清除软件包的执行日志。
• Retention Period (Days) – This is the number of days after which the logs can be cleaned up from the SSISDB database
  保留期限（天） –这是可以从SSISDB数据库中清除日志的天数
• Server-wide Default Logging Level – This value is used to set the default logging level for the packages if those are set to None during development. There are few different levels of logging starting from the Basic, Performance, Verbose and Runtime Lineage
  服务器范围的默认日志记录级别 –如果在开发过程中将软件包设置为“无”，则此值用于设置软件包的默认日志记录级别。 从“基本”，“性能”，“详细”和“运行时”世系开始，记录级别不同
• Maximum Number of Versions per Project – As it goes by the name, this property holds value for the maximum number of versions of the packages that can be retained by the catalog database. This is helpful if you want to revert to certain versions after a new version has been deployed
  每个项目的最大版本数 –顾名思义，此属性保存目录数据库可以保留的软件包最大版本数的值。 如果要在部署新版本后还原到某些版本，这将很有帮助
• Periodically Remove Old Versions – This is also a Boolean value, which if set to TRUE will keep the maximum number of versions with respect to the above property and will remove the older versions from the catalog database
  定期删除旧版本 –这也是一个布尔值，如果将其设置为TRUE，则将保留相对于上述属性的最大版本数，并将从目录数据库中删除旧版本

了解目录对象 (Understanding catalog objects)

Once the catalog database has been created, it is important to understand the architecture of the database and the various objects within it. The following diagram somewhat explains the architecture of the catalog database and how it relates to the SQL Server database engine.

创建目录数据库后，重要的是了解数据库的体系结构以及其中的各种对象。 下图在某种程度上说明了目录数据库的体系结构及其与SQL Server数据库引擎的关系。

Figure 4 – SSIS Catalog Database architecture

图4 – SSIS目录数据库架构

If you see in the diagram, the top-level component here is the SQL Server Database Engine, on which the Integration Services Catalog database resides. Inside the Catalog database, you can organize your SSIS projects in folders. This is just a logical grouping of folders that you can use to group your projects. Usually, I create folders specific to different customers and then deploy the projects within those folders.

如果您在图中看到，则此处的顶级组件是SQL Server数据库引擎，Integration Services目录数据库驻留在该SQL Server数据库引擎上。 在目录数据库中，您可以将SSIS项目组织在文件夹中。 这只是文件夹的逻辑分组，可用于对项目进行分组。 通常，我创建特定于不同客户的文件夹，然后在这些文件夹中部署项目。

Now, every folder in the catalog database is capable of one or multiple projects. As soon as you create a new folder, you can automatically see two subfolders being created within it – one for the Projects and one for the Environments.

现在，目录数据库中的每个文件夹都可以执行一个或多个项目。 创建新文件夹后，您会自动看到其中正在创建两个子文件夹-一个用于项目 ，一个用于环境 。

Figure 5 – SSISDB Folder Structure

图5 – SSISDB文件夹结构

The Projects folder contains the SSIS project that you deploy to the SSIS Catalog database which further contains the individual DTSX packages along with the following objects.

Projects文件夹包含您部署到SSIS Catalog数据库的SSIS项目，该数据库进一步包含各个DTSX软件包以及以下对象。

• SSIS Project and Package Parameters – If any project-level parameters were used in the project then you can configure those parameters in these settings
  SSIS项目和程序包参数 –如果项目中使用了任何项目级参数，则可以在这些设置中配置这些参数
• Environment References – This object is used to set the environment on which the packages are to be executed. You can configure multiple environments on the same machine and then just change the reference to use the desired environment as required
  环境参考 –此对象用于设置要在其上执行程序包的环境。 您可以在同一台计算机上配置多个环境，然后只需更改引用即可根据需要使用所需的环境
• Packages – All the packages that were designed in the project will be listed under this directory. You can also configure custom parameters for each of the packages separately
  软件包 –项目中设计的所有软件包都将在此目录下列出。 您还可以分别为每个软件包配置自定义参数

The Environments folder consists of the various environments that can be used for executing the SSIS packages (development, QA, staging, production etc.). You can create multiple environments, one for each as mentioned and then define the Environment Variables within each of them. These variables can contain connection strings for different databases, or other project or package parameter values based on the environment. This can be referred to as an alternate option for setting up configuration files.

Environments文件夹包含可用于执行SSIS包的各种环境（ 开发，QA，分段，生产等 ）。 您可以创建多个环境，为每个环境创建一个环境，然后在每个环境中定义环境变量 。 这些变量可以包含用于不同数据库的连接字符串，或基于环境的其他项目或程序包参数值。 这可以称为设置配置文件的替代选项。

Below is an example of how the entire SSIS catalog structure might look like.

以下是整个SSIS目录结构的外观示例。

Figure 6 – SSIS Catalog Database Structure

图6 – SSIS目录数据库结构

部署项目 (Deploying the projects)

Now that we have some idea about the Integration Services Catalog database structure, let us now understand how we can deploy the SSIS projects and packages to this catalog database after development. Basically, there are three modes of project deployment into the catalog database as follows.

现在，我们对Integration Services目录数据库的结构有了一些了解，现在让我们了解如何在开发后将SSIS项目和程序包部署到此目录数据库。 基本上，将项目部署到目录数据库的方式有以下三种。

• Using SQL Server Management Studio
  使用SQL Server Management Studio
• Using the SQL Server Data Tools / Visual Studio
  使用SQL Server数据工具/ Visual Studio
• Using TSQL scripts
  使用TSQL脚本

Well, as you might have already guessed, the most preferred way of deploying the projects would be by using the SQL Server Management Studio. It provides a graphical user interface of carrying out all the necessary actions required to deploy an Integration Services project. The final build of an SSIS project is an ISPAC file which contains all the packages within and can be used as a deployment file.

嗯，正如您可能已经猜到的那样，部署项目的最佳方法是使用SQL Server Management Studio。 它提供了图形用户界面，可以执行部署Integration Services项目所需的所有必要操作。 SSIS项目的最终版本是ISPAC文件，该文件包含其中的所有软件包，并且可以用作部署文件。

However, during the development phase, you might need to frequently need to redeploy the projects to verify certain features. In that case, you can deploy the project directly from the SQL Server Data Tools (SSDT) or Visual Studio. This helps the developers directly deploy the projects into the server without having to leave the development environment.

但是，在开发阶段，您可能经常需要重新部署项目以验证某些功能。 在这种情况下，您可以直接从SQL Server数据工具（SSDT）或Visual Studio部署项目。 这有助于开发人员将项目直接部署到服务器中，而不必离开开发环境。

Finally, another way to deploy SSIS packages to the SSIS catalog would be to use the T-SQL scripts. Using this feature gives the database administrators greater control over the deployment and configuration part and these scripts can also be used to automate certain deployments if required.

最后，将SSIS包部署到SSIS目录的另一种方法是使用T-SQL脚本。 使用此功能使数据库管理员可以更好地控制部署和配置部分，并且如果需要，这些脚本也可以用于自动执行某些部署。

结论 (Conclusion)

In this article, I have explained the SSIS Catalog database (SSISDB) that is created within the SQL Server instance. This catalog database is one of the most important concepts that need to be understood while dealing with SSIS project deployments. Using the catalog database, you can easily configure parameters, set environments, and manage other activities. To learn more about the catalog database, please refer to the official documentation from Microsoft.

在本文中，我已经解释了在SQL Server实例中创建的SSIS目录数据库（SSISDB）。 该目录数据库是在处理SSIS项目部署时需要理解的最重要的概念之一。 使用目录数据库，您可以轻松配置参数，设置环境以及管理其他活动。 要了解有关目录数据库的更多信息，请参阅Microsoft的官方文档 。

翻译自: https://www.sqlshack.com/introduction-to-the-ssis-catalog-database-ssisdb/

ssis 创建ssisdb