SSIS 目錄SSIS Catalog

APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

SSISDB 目錄是處理您已部署至 Integration ServicesIntegration Services 伺服器之 Integration ServicesIntegration Services (SSIS) 專案的中心點。The SSISDB catalog is the central point for working with Integration ServicesIntegration Services (SSIS) projects that you've deployed to the Integration ServicesIntegration Services server. 例如,您可以設定專案和封裝參數、設定環境以指定封裝的執行值、執行和疑難排解封裝,以及管理 Integration ServicesIntegration Services 伺服器作業。For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration ServicesIntegration Services server operations.

注意

本文描述一般 SSIS 目錄,以及在內部部署執行的 SSIS 目錄。This article describes the SSIS Catalog in general, and the SSIS Catalog running on premises. 您也可以在 Azure SQL Database 中建立 SSIS 目錄,並在 Azure 中部署和執行 SSIS 套件。You can also create the SSIS Catalog in Azure SQL Database, and deploy and run SSIS packages in Azure. 如需詳細資訊,請參閱將 SQL Server Integration Services 工作負載隨即轉移至雲端For more info, see Lift and shift SQL Server Integration Services workloads to the cloud.

雖然您也可以在 Linux 上執行 SSIS 套件,但 Linux 不支援 SSIS 目錄。Although you can also run SSIS packages on Linux, the SSIS Catalog is not supported on Linux. 如需詳細資訊,請參閱使用 SSIS 在 Linux 上擷取、轉換和載入資料For more info, see Extract, transform, and load data on Linux with SSIS.

儲存在 SSISDB 目錄中的物件包含專案、封裝、參數、環境和作業記錄。The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history.

您可以查詢 SSISDB 資料庫中的檢視表,以檢查儲存在 SSISDB 目錄中的物件、設定及作業資料。You inspect objects, settings, and operational data that are stored in the SSISDB catalog, by querying the views in the SSISDB database. 若要管理物件,請呼叫 SSISDB 資料庫中的預存程序或是使用 SSISDB 目錄的 UI。You manage the objects by calling stored procedures in the SSISDB database or by using the UI of the SSISDB catalog. 在許多情況下,可以在此 UI 中或是藉由呼叫預存程序來執行相同的工作。In many cases, the same task can be performed in the UI or by calling a stored procedure.

若要維護 SSISDB 資料庫,建議您套用管理使用者資料庫的標準企業原則。To maintain the SSISDB database, it is recommended that you apply standard enterprise policies for managing user databases. 如需有關建立維護計畫的詳細資訊,請參閱< Maintenance Plans>。For information about creating maintenance plans, see Maintenance Plans.

SSISDB 目錄和 SSISDB 資料庫都支援 Windows PowerShell。The SSISDB catalog and the SSISDB database support Windows PowerShell. 如需有關使用 SQL Server 搭配 Windows PowerShell 的詳細資訊,請參閱< SQL Server PowerShell>。For more information about using SQL Server with Windows PowerShell, see SQL Server PowerShell. 如需有關如何使用 Windows PowerShell 完成部署專案等工作的範例,請參閱 blogs.msdn.com 上的部落格文章: SQL Server 2012 中的 SSIS 和 PowerShellFor examples of how to use Windows PowerShell to complete tasks such as deploying a project, see the blog entry, SSIS and PowerShell in SQL Server 2012, on blogs.msdn.com.

如需檢視作業資料的詳細資訊,請參閱 監視封裝執行和其他作業For more information about viewing operations data, see Monitor Running Package and Other Operations.

若要在 中存取 SSISDB SQL Server Management StudioSQL Server Management Studio 目錄,請連接到 SQL ServerSQL Server Database Engine,然後在 [物件總管] 中展開 [Integration Services 目錄] 節點。You access the SSISDB catalog in SQL Server Management StudioSQL Server Management Studio by connecting to the SQL ServerSQL Server Database Engine and then expanding the Integration Services Catalogs node in Object Explorer. 若要在 中存取 SSISDB SQL Server Management StudioSQL Server Management Studio 資料庫,請在 [物件總管] 中展開 [資料庫] 節點。You access the SSISDB database in SQL Server Management StudioSQL Server Management Studio by expanding the Databases node in Object Explorer.

注意

您無法為 SSISDB 資料庫重新命名。You cannot rename the SSISDB database.

注意

如果附加 SQL ServerSQL Server SSISDB 資料庫的 執行個體停止或沒有回應,ISServerExec.exe 處理序便會結束。If the SQL ServerSQL Server instance that the SSISDB database is attached to, stops or does not respond, the ISServerExec.exe process ends. 會在 Windows 事件記錄檔中寫入一則訊息。A message is written to a Windows Event log.

如果 SQL ServerSQL Server 資源的容錯移轉是叢集容錯移轉的一部分,就不會重新啟動執行中的套件。If the SQL ServerSQL Server resources fail over as part of a cluster failover, the running packages do not restart. 您可以使用檢查點重新啟動封裝。You can use checkpoints to restart packages. 如需詳細資訊,請參閱 使用檢查點來重新啟動封裝For more information, see Restart Packages by Using Checkpoints.

特性與功能Features and capabilities

目錄物件識別碼Catalog Object Identifiers

當您在目錄中建立新的物件時,請為此物件指派名稱。When you create a new object in the catalog, assign a name to the object. 物件名稱是識別碼。The object name is an identifier. SQL ServerSQL Server 會定義哪些字元可以在識別碼中使用的規則。defines rules for which characters can be used in an identifier. 隨後的物件名稱必須遵照識別碼規則。Names for the following objects must follow identifier rules.

  • 資料夾Folder

  • 專案Project

  • 環境Environment

  • 參數Parameter

  • 環境變數Environment Variable

資料夾、專案、環境Folder, Project, Environment

在重新命名資料夾、專案或環境時,請考慮以下規則。Consider the following rules when renaming a folder, project, or environment.

  • 無效的字元包括 ASCII/Unicode 字元 1 到 31、引號 (")、小於 (<)、大於 (>)、直立線符號 (|)、退格鍵 (\b)、null (\0) 和 Tab 鍵 (\t)。Invalid characters include ASCII/Unicode characters 1 through 31, quote ("), less than (<), greater than (>), pipe (|), backspace (\b), null (\0), and tab (\t).

  • 名稱不得包含開頭或尾端空格。The name might not contain leading or trailing spaces.

  • 不允許以 @ 作為第一個字元,但隨後的字元可以使用 @。@ is not allowed as the first character, but subsequent characters might use @.

  • 名稱的長度必須大於 0 且小於或等於 128。The length of the name must be greater than 0 and less than or equal to 128.

參數Parameter

在命名參數時,請考慮以下規則。Consider the following rules when naming a parameter.

  • 名稱的第一個字元必須是 Unicode Standard 2.0 中所定義的字母,或是底線 ()。The first character of the name must be a letter as defined in the Unicode Standard 2.0, or an underscore ().

  • 後續的字元可以是 Unicode Standard 2.0 中定義的字母或數字,或是底線 ()。Subsequent characters can be letters or numbers as defined in the Unicode Standard 2.0, or an underscore ().

環境變數Environment Variable

在命名環境變數時,請考慮以下規則。Consider the following rules when naming an environment variable.

  • 無效的字元包括 ASCII/Unicode 字元 1 到 31、引號 (")、小於 (<)、大於 (>)、直立線符號 (|)、退格鍵 (\b)、null (\0) 和 Tab 鍵 (\t)。Invalid characters include ASCII/Unicode characters 1 through 31, quote ("), less than (<), greater than (>), pipe (|), backspace (\b), null (\0), and tab (\t).

  • 名稱不得包含開頭或尾端空格。The name might not contain leading or trailing spaces.

  • 不允許以 @ 作為第一個字元,但隨後的字元可以使用 @。@ is not allowed as the first character, but subsequent characters might use @.

  • 名稱的長度必須大於 0 且小於或等於 128。The length of the name must be greater than 0 and less than or equal to 128.

  • 名稱的第一個字元必須是 Unicode Standard 2.0 中所定義的字母,或是底線 ()。The first character of the name must be a letter as defined in the Unicode Standard 2.0, or an underscore ().

  • 後續的字元可以是 Unicode Standard 2.0 中定義的字母或數字,或是底線 ()。Subsequent characters can be letters or numbers as defined in the Unicode Standard 2.0, or an underscore ().

目錄組態Catalog Configuration

您會藉由調整目錄屬性來微調目錄的行為模式。You fine-tune how the catalog behaves by adjusting the catalog properties. 目錄屬性會定義如何加密敏感性資料以及如何保留作業和專案版本設定資料。Catalog properties define how sensitive data is encrypted, and how operations and project versioning data is retained. 若要設定目錄屬性,請使用 [目錄屬性] 對話方塊,或是呼叫 catalog.configure_catalog (SSISDB 資料庫) 預存程序。To set catalog properties, use the Catalog Properties dialog box or call the catalog.configure_catalog (SSISDB Database) stored procedure. 若要檢視屬性,請使用對話方塊或查詢 catalog.catalog_properties (SSISDB 資料庫)To view the properties, use the dialog box or query catalog.catalog_properties (SSISDB Database). 您可在 [物件總管] 中以滑鼠右鍵按一下 SSISDB 來存取此對話方塊。You access the dialog box by right-clicking SSISDB in Object Explorer.

作業和專案版本清除Operations and Project Version Cleanup

目錄中許多作業的狀態資料會儲存在內部資料庫資料表中。Status data for many of the operations in the catalog is stored in internal database tables. 例如,目錄會追蹤封裝執行和專案部署的狀態。For example, the catalog tracks the status of package executions and project deployments. 為了維護作業資料的大小, 中的 [SSIS Server 維護作業] SQL Server Management StudioSQL Server Management Studio 會用來移除舊的資料。To maintain the size of the operations data, the SSIS Server Maintenance Job in SQL Server Management StudioSQL Server Management Studio is used to remove old data. 安裝 SQL ServerSQL Server 時會建立此 Integration ServicesIntegration Services Agent 作業。This SQL ServerSQL Server Agent job is created when Integration ServicesIntegration Services is installed.

若要更新或重新部署 Integration ServicesIntegration Services 專案,請使用相同名稱將它部署到目錄中的相同資料夾。You can update or redeploy an Integration ServicesIntegration Services project by deploying it with the same name to the same folder in the catalog. 根據預設,每當您重新部署專案時, SSISDB 目錄都會保留此專案的舊版。By default, each time you redeploy a project, the SSISDB catalog retains the previous version of the project. 為了維護作業資料的大小, [SSIS Server 維護作業] 會用來移除專案的舊版。To maintain the size of the operations data, the SSIS Server Maintenance Job is used to remove old versions of projects.

為執行 SSIS Server 維護作業,SSIS 會建立 SQL Server 登入 ##MS_SSISServerCleanupJobLogin##To run the SSIS Server Maintenance Job, SSIS creates the SQL Server login ##MS_SSISServerCleanupJobLogin##. 此登入僅供 SSIS 內部使用。This login is only for internal use by SSIS.

以下 [SSISDB] 目錄屬性會定義此 SQL ServerSQL Server Agent 作業的行為模式。The following SSISDB catalog properties define how this SQL ServerSQL Server Agent job behaves. 您可以使用 [目錄屬性] 對話方塊或使用 catalog.catalog_properties (SSISDB 資料庫)catalog.configure_catalog (SSISDB 資料庫) 檢視及修改屬性。You can view and modify the properties by using the Catalog Properties dialog box or by using catalog.catalog_properties (SSISDB Database) and catalog.configure_catalog (SSISDB Database).

定期清除記錄檔Clean Logs Periodically
當這個屬性設定為 True時,便會執行作業清除的作業步驟。The job step for operations cleanup runs when this property is set to True.

保留週期 (天)Retention Period (days)
定義可允許的作業資料存在時間上限 (以天為單位)。Defines the maximum age of allowable operations data (in days). 移除較舊的資料。Older data are removed.

最小值是一天。The minimum value is one day. 最大值只會受到 SQL ServerSQL Server int 資料最大值的限制。The maximum value is limited only by the maximum value of the SQL ServerSQL Server int data. 如需此資料類型的資訊,請參閱 int、bigint、smallint 和 tinyint (Transact-SQL)For information about this data type, see int, bigint, smallint, and tinyint (Transact-SQL).

定期移除舊版本Periodically Remove Old Versions
當這個屬性設定為 True時,便會執行專案版本清除的作業步驟。The job step for project version cleanup runs when this property is set to True.

每一專案的版本數目上限Maximum Number of Versions per Project
定義多少個專案版本儲存在目錄中。Defines how many versions of a project are stored in the catalog. 移除專案的舊版。Older versions of projects are removed.

加密演算法Encryption Algorithm

[加密演算法] 屬性會指定用來加密敏感性參數值的加密類型。The Encryption Algorithm property specifies the type of encryption that is used to encrypt sensitive parameter values. 您可以從以下類型的加密中選擇。You can choose from the following types of encryption.

  • AES_256 (預設)AES_256 (default)

  • AES_192AES_192

  • AES_128AES_128

  • DESXDESX

  • TRIPLE_DES_3KEYTRIPLE_DES_3KEY

  • TRIPLE_DESTRIPLE_DES

  • DESDES

當您將 Integration ServicesIntegration Services 專案部署至 Integration ServicesIntegration Services 伺服器時,目錄會自動將封裝資料與敏感值加密。When you deploy an Integration ServicesIntegration Services project to the Integration ServicesIntegration Services server, the catalog automatically encrypts the package data and sensitive values. 當您擷取時,目錄也會自動解密資料。The catalog also automatically decrypts the data when you retrieve it. SSISDB 目錄會使用 ServerStorage 保護等級。The SSISDB catalog uses the ServerStorage protection level. 如需詳細資訊,請參閱 Access Control for Sensitive Data in PackagesFor more information, see Access Control for Sensitive Data in Packages.

變更加密演算法是需要大量時間的作業。Changing the encryption algorithm is a time-intensive operation. 首先,伺服器必須使用先前指定的演算法來解密所有組態值。First, the server has to use the previously specified algorithm to decrypt all configuration values. 然後,伺服器必須使用新的演算法來重新加密值。Then, the server has to use the new algorithm to re-encrypt the values. 在這段期間,伺服器上不能有其他的 Integration ServicesIntegration Services 作業。During this time, there cannot be other Integration ServicesIntegration Services operations on the server. 因此,為了讓 Integration ServicesIntegration Services 作業持續不受干擾,在 Management StudioManagement Studio的此對話方塊中,加密演算法會是唯讀值。Thus, to enable Integration ServicesIntegration Services operations to continue uninterrupted, the encryption algorithm is a read-only value in the dialog box in Management StudioManagement Studio.

若要變更 [加密演算法] 屬性設定,請將 SSISDB 資料庫設為單一使用者模式,然後呼叫 catalog.configure_catalog 預存程序。To change the Encryption Algorithm property setting, set the SSISDB database to the single-user mode and then call the catalog.configure_catalog stored procedure. 使用 ENCRYPTION_ALGORITHM 指定 property_name 引數。Use ENCRYPTION_ALGORITHM for the property_name argument. 如需支援的屬性值,請參閱 catalog.catalog_properties (SSISDB 資料庫)For the supported property values, see catalog.catalog_properties (SSISDB Database). 如需預存程序的詳細資訊,請參閱 catalog.configure_catalog (SSISDB 資料庫)For more information about the stored procedure, see catalog.configure_catalog (SSISDB Database).

如需單一使用者模式的詳細資訊,請參閱將資料庫設定為單一使用者模式For more information about single-user mode, see Set a Database to Single-user Mode. 如需 SQL ServerSQL Server中加密和加密演算法的資訊,請參閱 SQL Server 加密一節中的主題。For information about encryption and encryption algorithms in SQL ServerSQL Server, see the topics in the section, SQL Server Encryption.

資料庫主要金鑰會用於加密。A database master key is used for the encryption. 當您建立目錄時會建立此金鑰。The key is created when you create the catalog.

下表列出 [目錄屬性] 對話方塊中所顯示的屬性名稱,以及資料庫檢視中的對應屬性。The following table lists the property names shown in the Catalog Properties dialog box and the corresponding properties in the database view.

屬性名稱 ([目錄屬性] 對話方塊)Property Name (Catalog Properties dialog box) 屬性名稱 (資料庫檢視)Property Name (database view)
加密演算法名稱Encryption Algorithm Name ENCRYPTION_ALGORITHMENCRYPTION_ALGORITHM
定期清除記錄檔Clean Logs Periodically OPERATION_CLEANUP_ENABLED​OPERATION_CLEANUP_ENABLED​
保留週期 (天)Retention Period (days) RETENTION_WINDOWRETENTION_WINDOW
定期移除舊版本Periodically Remove Old Versions VERSION_CLEANUP_ENABLEDVERSION_CLEANUP_ENABLED
每一專案的版本數目上限Maximum Number of Versions per Project MAX_PROJECT_VERSIONSMAX_PROJECT_VERSIONS
全伺服器的預設記錄層次Server-wide Default Logging Level SERVER_LOGGING_LEVELSERVER_LOGGING_LEVEL

權限Permissions

專案、環境和封裝會包含在屬於安全性實體物件的資料夾中。Projects, environments, and packages are contained in folders that are securable objects. 您可以將權限授與資料夾,包括 MANAGE_OBJECT_PERMISSIONS 權限。You can grant permissions to a folder, including the MANAGE_OBJECT_PERMISSIONS permission. MANAGE_OBJECT_PERMISSIONS 可讓您將資料夾內容管理委派給使用者,而不必將使用者成員資格授與 ssis_admin 角色。MANAGE_OBJECT_PERMISSIONS enables you to delegate the administration of folder contents to a user without having to grant the user membership to the ssis_admin role. 您還可以授與權限給專案、環境和作業。You can also grant permissions to projects, environments, and operations. 作業包括初始化 Integration ServicesIntegration Services、部署專案、建立及啟動執行、驗證專案和封裝及設定 SSISDB 目錄。Operations include initializing Integration ServicesIntegration Services, deploying projects, creating and starting executions, validating projects and packages, and configuring the SSISDB catalog.

如需資料庫角色的詳細資訊,請參閱 資料庫層級角色For more information about database roles, see Database-Level Roles.

SSISDB 目錄會使用 DDL 觸發程序 ddl_cleanup_object_permissions 來強制 SSIS 安全性實體之權限資訊的完整性。The SSISDB catalog uses a DDL trigger, ddl_cleanup_object_permissions, to enforce the integrity of permissions information for SSIS securables. 當資料庫主體 (例如資料庫使用者、資料庫角色或資料庫應用程式角色) 從 SSISDB 資料庫中移除時,便會引發此觸發程序。The trigger fires when a database principal, such as a database user, database role, or a database application role, is removed from the SSISDB database.

如果此主體已被授與或拒絕其他主體的權限,請撤銷授與者所提供的權限,然後才可移除該主體。If the principal has granted or denied permissions to other principals, revoke the permissions given by the grantor, before the principal can be removed. 否則,當系統嘗試移除此主體時,便會傳回錯誤訊息。Otherwise, an error message is returned when the system tries to remove the principal. 此觸發程序會移除所有權限記錄,在這些記錄中,資料庫主體為被授與者。The trigger removes all permission records where the database principal is a grantee.

建議您不應該停用此觸發程序,因為它會確保從 SSISDB 資料庫卸除資料庫主體之後,不會有任何被遺棄的權限記錄。It is recommended that the trigger is not disabled because it ensures that are no orphaned permission records after a database principal is dropped from the SSISDB database.

管理權限Managing Permissions

您可以使用 SQL Server Management StudioSQL Server Management Studio UI、預存程序及 Microsoft.SqlServer.Management.IntegrationServices 命名空間來管理權限。You can manage permissions by using the SQL Server Management StudioSQL Server Management Studio UI, stored procedures, and the Microsoft.SqlServer.Management.IntegrationServices namespace.

若要使用 SQL Server Management StudioSQL Server Management Studio UI 來管理權限,請使用以下對話方塊:To manage permissions using the SQL Server Management StudioSQL Server Management Studio UI, use the following dialog boxes:

若要使用 Transact-SQL 來管理權限,請呼叫 catalog.grant_permission (SSISDB 資料庫)catalog.deny_permission (SSISDB 資料庫)catalog.revoke_permission (SSISDB 資料庫)To manage permissions using Transact-SQL, call catalog.grant_permission (SSISDB Database), catalog.deny_permission (SSISDB Database), and catalog.revoke_permission (SSISDB Database). 若要檢視對所有物件之目前主體有效的權限,請查詢 catalog.effective_object_permissions (SSISDB 資料庫)To view effective permissions for the current principal for all objects, query catalog.effective_object_permissions (SSISDB Database). 此主題會提供不同類型之權限的描述。This topic provides descriptions of the different types of permissions. 若要檢視已明確指派給使用者的權限,請查詢 catalog.explicit_object_permissions (SSISDB 資料庫)To view permissions that have been explicitly assigned to the user, query catalog.explicit_object_permissions (SSISDB Database).

資料夾Folders

資料夾包含 SSISDB 目錄中的一個或多個專案和環境。A folder contains one or more projects and environments in the SSISDB catalog. 您可以使用 catalog.folders (SSISDB 資料庫) 檢視來存取目錄中資料夾的相關資訊。You can use the catalog.folders (SSISDB Database) view to access information about folders in the catalog. 您可以使用以下預存程序來管理資料夾:You can use the following stored procedures to manage folders:

專案和套件Projects and Packages

每一個專案都可包含多個封裝。Each project can contain multiple packages. 專案和封裝都可以包含參數及環境的參考。Both projects and packages can contain parameters and references to environments. 您可以使用 Configure Dialog Box來存取參數和環境參考。You can access the parameters and environment references by using the Configure Dialog Box.

您可以藉由呼叫以下預存程序來完成其他專案工作:You can carry out other project tasks by calling the following stored procedures:

這些檢視表會提供有關封裝、專案和專案版本的詳細資料。These views provide details about packages, projects, and project versions.

參數Parameters

您在封裝執行時,可使用參數將值指派給封裝屬性。You use parameters to assign values to package properties at the time of package execution. 若要設定封裝或專案參數的值及清除該值,請呼叫 catalog.set_object_parameter_value (SSISDB 資料庫)catalog.clear_object_parameter_value (SSISDB 資料庫)To set the value of a package or project parameter and to clear the value, call catalog.set_object_parameter_value (SSISDB Database) and catalog.clear_object_parameter_value (SSISDB Database). 若要為執行的執行個體設定參數值,請呼叫 catalog.set_execution_parameter_value (SSISDB 資料庫)To set the value of a parameter for an instance of execution, call catalog.set_execution_parameter_value (SSISDB Database). 您可以藉由呼叫 catalog.get_parameter_values (SSISDB 資料庫) 擷取預設參數值。You can retrieve default parameter values by calling catalog.get_parameter_values (SSISDB Database).

這些檢視表會顯示所有封裝和專案的參數,以及用於執行執行個體的參數值。These views show the parameters for all packages and projects, and parameter values that are used for an instance of execution.

伺服器環境、伺服器變數和伺服器環境參考Server Environments, Server Variables, and Server Environment References

伺服器環境包含伺服器變數。Server environments contain server variables. Integration ServicesIntegration Services 伺服器上執行或驗證封裝時,可以使用變數值。The variable values can be used when a package is executed or validated on the Integration ServicesIntegration Services server.

以下預存程序可讓您執行環境與變數的許多其他管理工作。The following stored procedures enable you to perform many other management tasks for environments and variables.

您可以藉由呼叫 catalog.set_environment_variable_protection (SSISDB 資料庫) 預存程序,設定變數的敏感度位元。By calling the catalog.set_environment_variable_protection (SSISDB Database) stored procedure, you can set the sensitivity bit for a variable.

若要使用伺服器變數的值,請指定專案與伺服器環境之間的參考。To use the value of a server variable, specify the reference between the project and the server environment. 您可以使用以下預存程序來建立和刪除參考。You can use the following stored procedures to create and delete references. 您也可以指出環境是否位於與專案相同的資料夾中,或是在不同的資料夾中。You can also indicate whether the environment can be located in the same folder as the project or in a different folder.

如需有關環境和變數的其他詳細資料,請查詢這些檢視表。For more details about environments and variables, query these views.

執行和驗證Executions and Validations

執行是封裝執行的執行個體。An execution is an instance of a package execution. 呼叫 catalog.create_execution (SSISDB Database)catalog.start_execution (SSISDB Database) 可建立並開始執行。Call catalog.create_execution (SSISDB Database) and catalog.start_execution (SSISDB Database) to create and start an execution. 若要停止執行或封裝/專案驗證,請呼叫 catalog.stop_operation (SSISDB 資料庫)To stop an execution or a package/project validation, call catalog.stop_operation (SSISDB Database).

若要使執行中的封裝暫停並建立傾印檔案,請呼叫 catalog.create_execution_dump 預存程序。To cause a running package to pause and create a dump file, call the catalog.create_execution_dump stored procedure. 傾印檔案會提供有關封裝執行的資訊,可幫助您針對執行問題進行疑難排解。A dump file provides information about the execution of a package that can help you troubleshoot execution issues. 如需有關產生及設定傾印檔案的詳細資訊,請參閱< Generating Dump Files for Package Execution>。For more information about generating and configuring dump files, see Generating Dump Files for Package Execution.

如需有關作業期間所記錄之執行、驗證和訊息以及與錯誤相關之內容資訊的詳細資料,請查詢這些檢視表。For details about executions, validations, messages that are logged during operations, and contextual information related to errors, query these views.

您可以藉由呼叫 catalog.validate_project (SSISDB 資料庫)catalog.validate_package (SSISDB 資料庫) 預存程序來驗證專案與封裝。You can validate projects and packages by calling the catalog.validate_project (SSISDB Database) and catalog.validate_package (SSISDB Database) stored procedures. catalog.validations (SSISDB Database) 檢視會提供有關驗證的詳細資料,例如驗證時所考量的伺服器環境參考、這是相依性驗證還是完整驗證,以及使用 32 位元執行階段還是 64 位元執行階段來執行封裝。The catalog.validations (SSISDB Database) view provides details about validations such as the server environment references that are considered in the validation, whether it is a dependency validation or a full validation, and whether the 32-bit runtime or the 64-bit runtime is used to run the package.

建立 SSIS 目錄Create the SSIS Catalog

SQL Server Data ToolsSQL Server Data Tools中設計和測試封裝之後,可以將包含封裝的專案,部署到 Integration ServicesIntegration Services 伺服器。After you design and test packages in SQL Server Data ToolsSQL Server Data Tools, you can deploy the projects that contain the packages to an Integration ServicesIntegration Services server. 在您將專案部署至 Integration ServicesIntegration Services 伺服器之前,該伺服器必須包含 SSISDB 目錄。Before you can deploy the projects to the Integration ServicesIntegration Services server, the server must contain the SSISDB catalog. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 的安裝程式不會自動建立目錄,您必須依照下列指示手動建立目錄。The installation program for SQL Server 2012 (11.x)SQL Server 2012 (11.x) does not automatically create the catalog; you need to manually create the catalog by using the following instructions.

您可以在 SQL Server Management StudioSQL Server Management Studio中建立 SSISDB 目錄。You can create the SSISDB catalog in SQL Server Management StudioSQL Server Management Studio. 您也可以使用 Windows PowerShell 以程式設計方式建立目錄。You also create the catalog programmatically by using Windows PowerShell.

若要在 SQL Server Management Studio 中建立 SSISDB 目錄To create the SSISDB catalog in SQL Server Management Studio

  1. 開啟 SQL Server Management StudioSQL Server Management StudioOpen SQL Server Management StudioSQL Server Management Studio.

  2. 連接到 SQL ServerSQL Server Database Engine。Connect to the SQL ServerSQL Server Database Engine.

  3. 在 [物件總管] 中,展開伺服器節點,以滑鼠右鍵按一下 [Integration Services 目錄] 節點,然後按一下 [建立目錄]。In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.

  4. 按一下 [啟用 CLR 整合]Click Enable CLR Integration.

    目錄便會使用 CLR 預存程序。The catalog uses CLR stored procedures.

  5. 按一下 [在 SQL Server 啟動時允許自動執行 Integration Services 預存程序],讓 catalog.startup 預存程序會在每次 SSISSSIS 伺服器執行個體重新啟動時執行。Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSISSSIS server instance is restarted.

    預存程序會執行 SSISDB 目錄之作業狀態的維護。The stored procedure performs maintenance of the state of operations for the SSISDB catalog. 如果 SSISSSIS 伺服器執行個體關閉,它就會修正任何執行中套件的狀態。It fixes the status of any packages there were running if the SSISSSIS server instance goes down.

  6. 輸入密碼,然後按一下 [確定]Enter a password, and then click Ok.

    此密碼保護用來加密目錄資料的資料庫主要金鑰。The password protects the database master key that is used for encrypting the catalog data. 請將密碼儲存在安全位置。Save the password in a secure location. 建議您同時備份資料庫主要金鑰。It is recommended that you also back up the database master key. 如需相關資訊,請參閱 Back Up a Database Master KeyFor more information, see Back Up a Database Master Key.

若要以程式設計方式建立 SSISDB 目錄To create the SSISDB catalog programmatically

  1. 執行下列 PowerShell 指令碼:Execute the following PowerShell script:

    # Load the IntegrationServices Assembly  
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")  
    
    # Store the IntegrationServices Assembly namespace to avoid typing it every time  
    $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"  
    
    Write-Host "Connecting to server ..."  
    
    # Create a connection to the server  
    $sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"  
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString  
    
    # Create the Integration Services object  
    $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection  
    
    # Provision a new SSIS Catalog  
    $catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")  
    $catalog.Create()  
    
    

    如需如何使用 Windows PowerShell 和 Microsoft.SqlServer.Management.IntegrationServices 命名空間的其他範例,請參閱 blogs.msdn.com 上的部落格文章:SQL Server 2012 中的 SSIS 和 PowerShellFor more examples of how to use Windows PowerShell and the Microsoft.SqlServer.Management.IntegrationServices namespace, see the blog entry, SSIS and PowerShell in SQL Server 2012, on blogs.msdn.com. 如需此命名空間的概觀和程式碼範例,請參閱 blogs.msdn.com 上的部落格文章: SSIS 目錄管理物件模型初探For an overview of the namespace and code examples, see the blog entry, A Glimpse of the SSIS Catalog Managed Object Model, on blogs.msdn.com.

目錄屬性對話方塊Catalog Properties Dialog Box

使用 [目錄屬性] 對話方塊來設定 SSISDB 目錄。Use the Catalog Properties dialog box to configure the SSISDB catalog. 目錄屬性定義如何加密敏感性資料,如何保留作業和專案版本設定資料,以及何時驗證作業逾時。SSISDB 目錄是 Integration ServicesIntegration Services 專案、封裝、參數與環境的中央儲存和管理點。Catalog properties define how sensitive data is encrypted, how operations and project versioning data is retained, and when validation operations time out. The SSISDB catalog is a central storage and administration point for Integration ServicesIntegration Services projects, packages, parameters, and environments.

您也可以在 catalog.catalog_properties 檢視表中檢視目錄屬性,以及使用 catalog.configure_catalog 預存程序來設定屬性。You can also view catalog properties in the catalog.catalog_properties view, and set the properties by using the catalog.configure_catalog stored procedure. 如需詳細資訊,請參閱 catalog.catalog_properties (SSISDB 資料庫)catalog.configure_catalog (SSISDB 資料庫)For more information, see catalog.catalog_properties (SSISDB Database) and catalog.configure_catalog (SSISDB Database).

您想要做什麼事?What do you want to do?

開啟目錄屬性對話方塊Open the Catalog Properties Dialog Box

  1. 開啟 SQL ServerSQL ServerManagement StudioManagement StudioOpen SQL ServerSQL ServerManagement StudioManagement Studio.

  2. 連接 Microsoft SQL Server Database Engine。Connect Microsoft SQL Server Database Engine.

  3. 在物件總管中,展開 [Integration Services] 節點,並以滑鼠右鍵按一下 [SSISDB],然後按一下 [屬性]。In Object Explorer, expand the Integration Services node, right-click SSISDB, and then click Properties.

設定選項Configure the Options

選項。Options

下表描述對話方塊中的特定屬性,以及 catalog.catalog_properties 檢視表中的對應屬性。The following table describes certain properties in the dialog box and the corresponding properties in the catalog.catalog_properties view.

屬性名稱 (目錄屬性對話方塊)Property Name (Catalog Properties dialog box) 屬性名稱 (catalog.catalog_properties 檢視表)Property Name (catalog.catalog_properties view) DescriptionDescription
加密演算法名稱Encryption Algorithm Name ENCRYPTION_ALGORITHMENCRYPTION_ALGORITHM 指定用來加密目錄中敏感性參數值的加密類型。Specifies the type of encryption that is used to encrypt the sensitive parameter values in the catalog. 以下是可能的值:The following are the possible values:

DESDES

TRIPLE_DESTRIPLE_DES

TRIPLE_DES_3KEYTRIPLE_DES_3KEY

DESPXDESPX

AES_128AES_128

AES_192AES_192

AES_256 (預設)AES_256 (default)
每一專案的版本數目上限Maximum Number of Versions per Project MAX_PROJECT_VERSIONSMAX_PROJECT_VERSIONS 指定目錄中所儲存的專案版本數目。Specify how many versions of a project are stored in the catalog. 當專案版本清除作業執行時,會移除超過上限的舊專案版本。Older versions of projects that exceed the maximum are removed when the project version cleanup job runs.
定期清除記錄檔Clean Logs Periodically OPERATION_CLEANUP_ENABLEDOPERATION_CLEANUP_ENABLED 將屬性設為 True,指出 SQL Server Agent 作業 (作業清除) 會執行。Set the property to True to indicate that the SQL Server Agent job, operations cleanup, runs. 否則請將屬性設為 False。Otherwise, set the property to False.
保留週期 (天)Retention Period (days) RETENTION_WINDOWRETENTION_WINDOW 指定可允許的作業資料存在時間上限 (以天為單位)。Specify the maximum age of allowable operations data (in days). SQL Agent 作業 (作業清除) 會移除比指定天數還舊的資料。Data that is older than the specified number of days are removed by the SQL Agent job, operations cleanup.

備份、還原和移動 SSIS 目錄Back up, Restore, and Move the SSIS Catalog

適用於: 是SQL Server (從 2016 開始) 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲APPLIES TO: yesSQL Server (starting with 2016) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2017 Integration Services (SSIS)SQL Server 2017 Integration Services (SSIS) 包括 SSISDB 資料庫。includes the SSISDB database. 您可以查詢 SSISDB 資料庫中的檢視,以檢查物件、設定以及儲存在 [SSISDB] 目錄中的作業資料。You query views in the SSISDB database to inspect objects, settings, and operational data that are stored in the SSISDB catalog. 本主題提供備份與還原資料庫的指示。This topic provides instructions for backing up and restoring the database.

SSISDB 目錄會儲存您已經部署到 Integration ServicesIntegration Services 伺服器的封裝。The SSISDB catalog stores the packages that you've deployed to the Integration ServicesIntegration Services server. 如需目錄的詳細資訊,請參閱 SSIS 目錄For more information about the catalog, see SSIS Catalog.

若要備份 SSIS 資料庫To Back up the SSIS Database

  1. 開啟 SQL Server Management StudioSQL Server Management Studio 並連接至 SQL ServerSQL Server的執行個體。Open SQL Server Management StudioSQL Server Management Studio and connect to an instance of SQL ServerSQL Server.

  2. 使用 BACKUP MASTER KEY Transact-SQL 陳述式,備份 SSISDB 資料庫的主要金鑰。Back up the master key for the SSISDB database, by using the BACKUP MASTER KEY Transact-SQL statement. 此金鑰儲存在您指定的檔案。The key is stored in a file that you specify. 使用密碼來加密檔案中的主要金鑰密碼。Use a password to encrypt the master key in the file.

    如需陳述式的詳細資訊,請參閱 BACKUP MASTER KEY (Transact-SQL)For more information about the statement, see BACKUP MASTER KEY (Transact-SQL).

    在下列範例中,主要金鑰是匯出至 c:\temp directory\RCTestInstKey 檔案。In the following example, the master key is exported to the c:\temp directory\RCTestInstKey file. LS2Setup! 密碼是用來加密主要金鑰。The LS2Setup! password is used to encrypt the master key.

    backup master key to file = 'c:\temp\RCTestInstKey'  
           encryption by password = 'LS2Setup!'  
    
    
  3. 使用 SQL Server Management StudioSQL Server Management Studio 中的 [備份資料庫] 對話方塊備份 SSISDB 資料庫。Back up the SSISDB database by using the Backup Database dialog box in SQL Server Management StudioSQL Server Management Studio. 如需詳細資訊,請參閱如何:備份資料庫 (SQL Server Management Studio)For more information, see How to: Back Up a Database (SQL Server Management Studio).

  4. 執行下列動作,以產生 ##MS_SSISServerCleanupJobLogin## 的 CREATE LOGIN 指令碼。Generate the CREATE LOGIN script for ##MS_SSISServerCleanupJobLogin##, by doing the following things. 如需詳細資訊,請參閱 CREATE LOGIN (Transact-SQL)For more information, see CREATE LOGIN (Transact-SQL).

    1. SQL Server Management StudioSQL Server Management Studio 的物件總管中,展開 [安全性] 節點,然後展開 [登入] 節點。In Object Explorer in SQL Server Management StudioSQL Server Management Studio, expand the Security node and then expand the Logins node.

    2. 以滑鼠右鍵按一下 [##MS_SSISServerCleanupJobLogin##],然後按一下 [編寫登入的指令碼為] > [CREATE 至] > [新增查詢編輯器視窗]。Right-click ##MS_SSISServerCleanupJobLogin##, and then click Script Login as > CREATE To > New Query Editor Window.

  5. 如果您要將 SSISDB 資料庫還原至從未建立過 SSISDB 目錄的 SQL ServerSQL Server 執行個體,請執行下列動作,以產生 sp_ssis_startup 的 CREATE PROCEDURE 指令碼。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, generate the CREATE PROCEDURE script for sp_ssis_startup, by doing the following things. 如需詳細資訊,請參閱 CREATE PROCEDURE (Transact-SQL)For more information, see CREATE PROCEDURE (Transact-SQL).

    1. 在物件總管中,展開 [資料庫] 節點,然後展開 [master] > [可程式性] > [預存程序] 節點。In Object Explorer, expand the Databases node and then expand the master > Programmability > Stored Procedures node.

    2. 以滑鼠右鍵按一下 dbo.sp_ssis_startup,然後按一下 [編寫預存程序的指令碼為] > [CREATE 至] > [新增查詢編輯器視窗]。Right-click dbo.sp_ssis_startup, and then click Script Stored Procedure as > CREATE To > New Query Editor Window.

  6. 確認已啟動 SQL Server AgentConfirm that SQL Server Agent has been started

  7. 如果您要將 SSISDB 資料庫還原至從未建立過 SSISDB 目錄的 SQL ServerSQL Server 執行個體,請執行下列動作,以產生 SSIS 伺服器維護作業的指令碼。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, generate a script for the SSIS Server Maintenance Job by doing the following things. 當您建立 SSISDB 目錄時,系統就會自動在 SQL ServerSQL Server Agent 中建立指令碼。The script is created in SQL ServerSQL Server Agent automatically when the SSISDB catalog is created. 此作業有助於清除保留週期外部的清除作業,並移除舊版專案。The job helps clean up cleanup operation logs outside the retention window and remove older versions of projects.

    1. 在物件總管中,展開 [SQL Server Agent] 節點,然後展開 [作業] 節點。In Object Explorer, expand the SQL Server Agent node and then expand the Jobs node.

    2. 以滑鼠右鍵按一下 SSIS 伺服器維護作業,然後按一下 [編寫作業的指令碼為] > [CREATE 至] > [新增查詢編輯器視窗]。Right-click SSIS Server Maintenance Job, and then click Script Job as > CREATE To > New Query Editor Window.

若要還原 SSIS 資料庫To Restore the SSIS Database

  1. 如果您要將 SSISDB 資料庫還原至從未建立過 SSISDB 目錄的 SQL ServerSQL Server 執行個體,請執行 sp_configure 預存程序來啟用 Common Language Runtime (CLR)。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr) by running the sp_configure stored procedure. 如需詳細資訊,請參閱 sp_configure (Transact-SQL)CLR 已啟用選項For more information, see sp_configure (Transact-SQL) and clr enabled Option.

    use master   
           sp_configure 'clr enabled', 1  
           reconfigure  
    
    
  2. 如果您要將 SSISDB 資料庫還原至從未建立過 SSISDB 目錄的 SQL ServerSQL Server 執行個體,請建立非對稱金鑰並根據非對稱金鑰建立登入,並且將 UNSAFE 權限授與登入。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, create the asymmetric key and the login from the asymmetric key, and grant UNSAFE permission to the login.

    Create Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey  
           FROM Executable File = 'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'  
    
    

    Integration ServicesIntegration Services CLR 預存程序需要授與 UNSAFE 權限給登入,因為登入需要對於限制資源的額外存取,例如 Microsoft Win32 API。CLR stored procedures require UNSAFE permissions to be granted to the login because the login requires additional access to restricted resources, such as the Microsoft Win32 API. 如需 UNSAFE 程式碼權限的詳細資訊,請參閱 建立組件For more information about the UNSAFE code permission, see Creating an Assembly.

    Create Login MS_SQLEnableSystemAssemblyLoadingUser  
           FROM Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey   
    
           Grant unsafe Assembly to MS_SQLEnableSystemAssemblyLoadingUser  
    
    
  3. 使用 SQL Server Management StudioSQL Server Management Studio 中的 [還原資料庫] 對話方塊,從備份還原 SSISDB 資料庫。Restore the SSISDB database from the backup by using the Restore Database dialog box in SQL Server Management StudioSQL Server Management Studio. 如需詳細資訊,請參閱下列主題:For more information, see the following topics:

  4. 針對 ##MS_SSISServerCleanupJobLogin##、sp_ssis_startup 和 SSIS 伺服器維護作業,執行您在 備份 SSIS 資料庫 中所建立的指令碼。Execute the scripts that you created in the To Back up the SSIS Database for ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup, and SSIS Server Maintenance Job. 確認已啟動 SQL Server Agent。Confirm that SQL Server Agent has been started.

  5. 執行下列陳述式以設定 sp_ssis_startup 程序進行自動執行。Run the following statement to set the sp_ssis_startup procedure for autoexecution. 如需詳細資訊,請參閱 sp_procoption (Transact-SQL)For more information, see sp_procoption (Transact-SQL).

    EXEC sp_procoption N'sp_ssis_startup','startup','on'  
    
  6. 使用 SQL Server Management StudioSQL Server Management Studio 中的 [登入屬性] 對話方塊,將 SSISDB 使用者 ##MS_SSISServerCleanupJobUser## (SSISDB 資料庫) 對應至 ##MS_SSISServerCleanupJobLogin##。Map the SSISDB user ##MS_SSISServerCleanupJobUser## (SSISDB database) to ##MS_SSISServerCleanupJobLogin##, by using the Login Properties dialog box in SQL Server Management StudioSQL Server Management Studio.

  7. 使用下列其中一種方法來還原主要金鑰。Restore the master key by using one of the following methods. 如需加密的詳細資訊,請參閱 加密階層For more information about encryption, see Encryption Hierarchy.

    • 方法 1Method 1

      如果您已經備份資料庫主要金鑰,而且您有用來加密主要金鑰的密碼,請使用此方法。Use this method if you've already performed a backup of the database master key, and you have the password used to encrypt the master key.

             Restore master key from file = 'c:\temp\RCTestInstKey'  
             Decryption by password = 'LS2Setup!' -- 'Password used to encrypt the master key during SSISDB backup'  
             Encryption by password = 'LS3Setup!' -- 'New Password'  
             Force  
      
      

      注意

      確認 SQL ServerSQL Server 服務帳戶擁有讀取備份金鑰檔案的權限。Confirm that the SQL ServerSQL Server service account has permissions to read the backup key file.

      注意

      如果服務主要金鑰尚未加密資料庫主要金鑰,SQL Server Management StudioSQL Server Management Studio 中即會顯示下列警告訊息。You see the following warning message displayed in SQL Server Management StudioSQL Server Management Studio if the database master key has not yet been encrypted by the service master key. 忽略警告訊息。Ignore the warning message.

      無法解密目前的主要金鑰。因為指定了 FORCE 選項,因此忽略了這個錯誤。The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

      FORCE 引數會指定即使未開放目前的資料庫主要金鑰,也應該繼續還原程序。The FORCE argument specifies that the restore process should continue even if the current database master key is not open. 針對 SSISDB 目錄,因為尚未在您要還原資料庫的目標執行個體上開啟資料庫主要金鑰,因此您會看到此訊息。For the SSISDB catalog, because the database master key has not been opened on the instance where you are restoring the database, you see this message.

    • 方法 2Method 2

      如果您有建立 SSISDB 所使用的原始密碼,請使用此方法。Use this method if you have the original password that was used to create SSISDB.

      open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'  
             Alter Master Key Add encryption by Service Master Key  
      
  8. 執行 Integration ServicesIntegration Services catalog.check_schema_version ,以判斷 SSISDB 目錄結構描述與二進位檔 (ISServerExec 和 SQLCLR 組件) 是否相容。Determine whether the SSISDB catalog schema and the Integration ServicesIntegration Services binaries (ISServerExec and SQLCLR assembly) are compatible, by running catalog.check_schema_version.

  9. 若要確認已成功還原 SSISDB 資料庫,請針對 SSISDB 目錄執行作業,例如執行已經部署到 Integration ServicesIntegration Services 伺服器的封裝。To confirm that the SSISDB database has been restored successfully, perform operations against the SSISDB catalog such as running packages that have been deployed to the Integration ServicesIntegration Services server. 如需詳細資訊,請參閱執行 Integration Services (SSIS) 套件For more information, see Run Integration Services (SSIS) Packages.

若要移動 SSIS 資料庫To Move the SSIS Database

  • 遵循移動使用者資料庫的指示進行。Follow the instructions for moving user databases. 如需詳細資訊,請參閱 移動使用者資料庫For more information, see Move User Databases.

    確定您有備份 SSISDB 資料庫的主要金鑰並保護備份檔案。Ensure that you back up the master key for the SSISDB database and protect the backup file. 如需詳細資訊,請參閱 備份 SSIS 資料庫For more information, see To Back up the SSIS Database.

    確定 Integration Services (SSIS) 相關物件建立在尚未建立 SSISDB 目錄的新 SQL ServerSQL Server 執行個體中。Ensure that the Integration Services (SSIS) relevant objects are created in the new SQL ServerSQL Server instance where the SSISDB catalog has not yet been created.

升級 SSIS 目錄 (SSISDB)Upgrade the SSIS Catalog (SSISDB)

當資料庫版本比目前的 SQL Server 執行個體版本還舊時,執行 SSISDB 升級精靈來升級 SSIS 目錄資料庫 (SSISDB)。Run the SSISDB Upgrade Wizard to upgrade the SSIS Catalog database, SSISDB, when the database is older than the current version of the SQL Server instance. 當下列其中一個條件成立時,資料庫可能為舊版。The database may be older when one of the following conditions is true.

  • 您已從舊版 SQL Server 還原資料庫。You restored the database from an older version of SQL Server.

  • 您在升級 SQL Server 執行個體之前,並未從 AlwaysOn 可用性群組移除資料庫。You did not remove the database from an Always On Availability Group before upgrading the SQL Server instance. 此條件可防止資料庫自動升級。This condition prevents the automatic upgrade of the database. 如需詳細資訊,請參閱< Upgrading SSISDB in an availability group>。For more info, see Upgrading SSISDB in an availability group.

此精靈只能升級本機伺服器執行個體上的資料庫。The wizard can only upgrade the database on a local server instance.

執行 SSISDB 升級精靈升級 SSIS 目錄 (SSISDB)Upgrade the SSIS Catalog (SSISDB) by running the SSISDB Upgrade Wizard

  1. 備份 SSIS 目錄資料庫 (SSISDB)。Back up the SSIS Catalog database, SSISDB.

  2. SQL Server Management StudioSQL Server Management Studio中,展開本機伺服器,然後展開 [Integration Services 目錄] 。In SQL Server Management StudioSQL Server Management Studio, expand the local server, and then expand Integration Services Catalogs.

  3. 以滑鼠右鍵按一下 [SSISDB],然後選取 [資料庫升級] 啟動 [SSISDB 升級精靈]。Right-click on SSISDB, and then select Database Upgrade to launch the SSISDB Upgrade Wizard. 或者,在本機伺服器上以較高的權限執行 C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe,以啟動 [SSISDB 升級精靈]。Or launch the SSISDB Upgrade Wizard by running C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe with elevated permissions on the local server.

    啟動 SSISDB 升級精靈

  4. 在 [選取執行個體] 頁面上,選取本機伺服器上的 SQL Server 執行個體。On the Select Instance page, select a SQL Server instance on the local server.

    重要

    此精靈只能升級本機伺服器執行個體上的資料庫。The wizard can only upgrade the database on a local server instance.

    選取此核取方塊,表示您已經在執行這個精靈之前備份 SSISDB 資料庫。Select the checkbox to indicate that you have backed up the SSISDB database before running the wizard.

    選取 [SSISDB 升級精靈] 中的伺服器Select the server in the SSISDB Upgrade Wizard

  5. 選取 [升級] 升級 SSIS 目錄資料庫。Select Upgrade to upgrade the SSIS Catalog database.

  6. 在 [結果] 頁面上,檢閱結果。On the Result page, review the results.

    檢閱 [SSISDB 升級精靈] 中的結果Review the results in the SSISDB Upgrade Wizard

適用於 SSIS 目錄 (SSISDB) 的 Always OnAlways On for SSIS Catalog (SSISDB)

AlwaysOn 可用性群組功能是提供資料庫鏡像之企業級替代方案的高可用性與災害復原解決方案。The Always On Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. 可用性群組支援一組可一起容錯移轉之離散化使用者資料庫的容錯移轉環境,也就是所謂的可用性資料庫。An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. 如需詳細資訊,請參閱 AlwaysOn 可用性群組 (SQL Server)For more information, see Always On Availability Groups.

為了提供 SSIS 目錄 (SSISDB) 及其內容 (專案、封裝、執行記錄等) 的高可用性,您可以將 SSISDB 資料庫 (就像任何其他使用者資料庫) 加入 AlwaysOn 可用性群組。In order to provide the high-availability for the SSIS catalog (SSISDB) and its contents (projects, packages, execution logs, etc.), you can add the SSISDB database (just the same as any other user database) to an Always On Availability Group. 發生容錯移轉時,其中一個次要節點會自動變成新的主要節點。When a failover occurs, one of the secondary nodes automatically becomes the new primary node.

重要

在容錯移轉時,執行中的套件不會重新啟動或繼續。When a failover occurs, packages that were running do not restart or resume.

本節內容:In this section:

  1. 必要條件Prerequisites

  2. 設定適用於 AlwaysOn 的 SSIS 支援Configure SSIS support for Always On

  3. 在可用性群組中升級 SSISDBUpgrading SSISDB in an availability group

必要條件Prerequisites

針對 SSISDB 資料庫啟用 Always On 支援之前,請先執行下列必要條件步驟。Do the following prerequisite steps before enabling Always On support for the SSISDB database.

  1. 設定 Windows 容錯移轉叢集。Set up a Windows failover cluster. 如需相關指示,請參閱 安裝適用於 Windows Server 2012 的容錯移轉叢集功能和工具 部落格文章。See Installing the Failover Cluster Feature and Tools for Windows Server 2012 blog post for instructions. 在所有叢集節點上安裝功能和工具。Install the feature and tools on all cluster nodes.

  2. 在叢集的每個節點上,安裝含有 Integration Services (SSIS) 功能的 SQL Server 2016。Install SQL Server 2016 with Integration Services (SSIS) feature on each node of the cluster.

  3. 啟用每個 SQL Server 執行個體的 Always On 可用性群組。Enable Always On Availability Groups for each SQL Server instance. 如需詳細資訊,請參閱 啟用和停用 AlwaysOn 可用性群組 (SQL Server)See Enable Always On Availability Groups for details.

設定適用於 AlwaysOn 的 SSIS 支援Configure SSIS support for Always On

重要

  • 您必須在可用性群組的 主要節點 上執行這些步驟。You must perform these steps on the primary node of the availability group.
  • 將 SSISDB 新增至 Always On 群組之後,您必須啟用適用於 Always On 的 SSIS 支援You must enable SSIS support for Always On after you add SSISDB to an Always On Availability Group.

注意

如需此程序的詳細資訊,請參閱下列由 Data Platform MVP Marcos Freccia 提供的逐步解說與其他螢幕擷取畫面:Adding SSISDB to AG for SQL Server 2016 (將 SSISDB 新增至 SQL Server 2016 的 AG)。For more info about this procedure, see the following walkthrough with additional screen shots by Data Platform MVP Marcos Freccia: Adding SSISDB to AG for SQL Server 2016.

步驟 1:建立 Integration Services 目錄Step 1: Create Integration Services Catalog

  1. 啟動 SQL Server Management Studio 並連接到您想要在叢集中設定為適用於 SSISDB 的 AlwaysOn 高可用性群組 主要節點 的 SQL Server 執行個體。Launch SQL Server Management Studio and connect to a SQL Server instance in the cluster that you want to set as the primary node of Always On high availability group for SSISDB.

  2. 在物件總管中,展開伺服器節點,以滑鼠右鍵按一下 [Integration Services 目錄] 節點,然後按一下 [建立目錄] 。In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.

  3. 按一下 [啟用 CLR 整合]Click Enable CLR Integration. 目錄便會使用 CLR 預存程序。The catalog uses CLR stored procedures.

  4. 按一下 [在 SQL Server 啟動時允許自動執行 Integration Services 預存程序] ,讓 catalog.startup 預存程序會在每次 SSIS 伺服器執行個體重新啟動時執行。Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSIS server instance is restarted. 預存程序會執行 SSISDB 目錄之作業狀態的維護。The stored procedure performs maintenance of the state of operations for the SSISDB catalog. 它會在 SSIS 伺服器執行個體效能降低時,修正任何正在執行之封裝的狀態。It fixes the status of any packages there were running if and when the SSIS server instance goes down.

  5. 輸入 密碼,然後按一下 [確定] 。Enter a password, and then click Ok. 此密碼保護用來加密目錄資料的資料庫主要金鑰。The password protects the database master key that is used for encrypting the catalog data. 請將密碼儲存在安全位置。Save the password in a secure location. 建議您同時備份資料庫主要金鑰。It is recommended that you also back up the database master key. 如需相關資訊,請參閱 Back Up a Database Master KeyFor more information, see Back Up a Database Master Key.

步驟 2:將 SSISDB 新增至 Always On 可用性群組Step 2: Add SSISDB to an Always On Availability Group

將 SSISDB 資料庫加入 AlwaysOn 可用性群組,幾乎等於是將任何其他使用者資料庫加入可用性群組。Adding the SSISDB database to an Always On Availability Group is almost same as adding any other user database into an availability group. 請參閱 使用可用性群組精靈See Use the Availability Group Wizard.

提供您在 [新增可用性群組] 精靈的 [選取資料庫] 頁面中建立 SSIS 目錄時指定的密碼。Provide the password that you specified while creating the SSIS Catalog in the Select Databases page of the New Availability Group wizard.

新增可用性群組New Availability Group

步驟 3:啟用適用於 Always On 的 SSIS 支援Step 3: Enable SSIS support for Always On

建立 Integration Services 目錄之後,以滑鼠右鍵按一下 [Integration Services 目錄] 節點,然後按一下 [啟用 Always On 支援]。After you create the Integration Service Catalog, right-click the Integration Service Catalogs node, and click Enable Always On Support. 您應該會看到下列 [啟用 AlwaysOn 支援] 對話方塊。You should see the following Enable Support for Always On dialog box. 如果這個功能表項目已停用,請確認您已安裝的所有必要條件,然後按一下 [重新整理] 。If this menu item is disabled, confirm that you have all the prerequisites installed and click Refresh.

啟用 Always On 支援

警告

在您啟用適用於 AlwaysOn 的 SSIS 支援之前,不支援自動容錯移轉 SSISDB 資料庫。Auto-failover of SSISDB database is not supported until you enable SSIS Support for Always On.

表格會顯示剛從 Always On 可用性群組新增的次要複本。The newly added secondary replicas from the Always On availability group are shown in the table. 針對清單中的每個複本按一下 [連接...] 按鈕,然後輸入驗證認證以連接到複本。Click Connect... button for each replica in the list and enter authentication credentials to connect to the replica. 使用者帳戶必須是每個複本上的系統管理員群組成員,才能啟用 SSIS 的 Always On 支援。The user account must be a member of sysadmin group on each replica to enable SSIS support for Always On. 當您成功連接到每個複本之後,按一下 [確定] 以啟用適用於 AlwaysOn 的 SSIS 支援。After you successfully connect to each replica, click OK to enable SSIS support for Always On.

在您完成其他必要條件之後,如果操作功能表上的 [啟用 Always On 支援] 選項顯示為停用,請嘗試下列方法:If the Enable Always On support option on the context menu appears to be disabled after you've completed the other prerequisites, try these things:

  1. 按一下 [重新整理] 選項,以重新整理操作功能表。Refresh the context menu by clicking the Refresh option.
  2. 請確定您已連線到主要節點。Make sure you are connecting to the primary node. 您必須啟用主要節點上的 Always On 支援。You have to enable Always On support on the primary node.
  3. 請確定 SQL Server 版本為 13.0 或更新版本。Make sure the SQL Server version is 13.0 or higher. 只有在 SQL Server 2016 和更新版本上,SSIS 才會支援 Always On。SSIS supports Always On only on SQL Server 2016 and later versions.

在可用性群組中升級 SSISDBUpgrading SSISDB in an availability group

如果您要從先前的版本升級 SQL Server,而且 SSISDB 在 AlwaysOn 可用性群組中,則您的升級可能會遭到「SSISDB 在 AlwaysOn 可用性群組中檢查」規則所封鎖。If you're upgrading SQL Server from a previous version, and SSISDB is in an Always On availability group, your upgrade may be blocked by the "SSISDB in Always On Availability Group check" rule. 因為升級是在單一使用者模式中執行,而可用性資料庫必須是多使用者資料庫,就會發生此封鎖。This blocking occurs because upgrade runs in single-user mode, while an availability database must be a multi-user database. 因此,在升級或修補期間,所有的可用性資料庫 (包括 SSISDB) 都要離線,而且不會進行升級或修補。Therefore, during upgrade or patching, all availability databases including SSISDB are taken offline and are not upgraded or patched. 若要讓升級繼續,請先從可用性群組移除 SSISDB,再升級或修補每個節點,然後將 SSISDB 新增回可用性群組。To let upgrade continue, first remove SSISDB from the availability group, then upgrade or patch each node, then add SSISDB back to the availability group.

如果您被「AlwaysOn 可用性群組中的 SSISDB 檢查規則」封鎖,就必須遵循這些步驟來升級 SQL Server。If you are blocked by the "SSISDB in Always On Availability Group check" rule, follow these steps to upgrade SQL Server.

  1. 從可用性群組中移除 SSISDB 資料庫。Remove the SSISDB database from the availability group. 如需詳細資訊,請參閱將次要資料庫從可用性群組移除 (SQL Server)將主要資料庫從可用性群組移除 (SQL Server)For more info, see Remove a Secondary Database from an Availability Group (SQL Server) and Remove a Primary Database from an Availability Group (SQL Server).

  2. 按一下升級精靈中的 [重新執行]。Click Rerun in the upgrade wizard. 即可通過「Always On 可用性群組中的 SSISDB 檢查」規則。The "SSISDB in Always On Availability Group check" rule passes.

  3. 按 [下一步] 繼續升級。Click the Next to continue the upgrade.

  4. 當您已升級所有節點之後,請將 SSISDB 資料庫加回 AlwaysOn 可用性群組。After you have upgraded all the nodes, add the SSISDB database back to the Always On availability group. 如需詳細資訊,請參閱將資料庫加入至可用性群組 (SQL Server)For more info, see Add a Database to an Availability Group (SQL Server).

如果您在升級 SQL Server 時未遭到封鎖,且 SSISDB 在 Always On 可用性群組中,請在升級 SQL Server 資料庫引擎之後個別升級 SSISDB。If you're not blocked when you upgrade SQL Server, and SSISDB is in an Always On availability group, upgrade SSISDB separately after you upgrade the SQL Server database engine. 使用 SSIS 升級精靈來升級 SSISDB,如下列程序所述。Use the SSIS Upgrade Wizard to upgrade the SSISDB as described in the following procedure.

  1. 將 SSISDB 資料庫移出可用性群組,或者如果 SSISDB 是可用性群組中唯一的資料庫,請刪除可用性群組。Move the SSISDB database out of the availability group, or delete the availability group if SSISDB is the only database in the availability group. 若要執行這項工作,請在可用性群組的主要節點上啟動 [SQL Server Management Studio]。Launch SQL Server Management Studio on the primary node of the availability group to perform this task.

  2. 從所有 複本節點移除 SSISDB 資料庫。Remove the SSISDB database from all replica nodes.

  3. 主要節點上升級 SSISDB 資料庫。Upgrade the SSISDB database on the primary node. 在 SQL Server Management Studio 的物件總管 中,展開 [Integration Services 目錄] 、以滑鼠右鍵按一下 [SSISDB] ,然後選取 [資料庫升級] 。InObject Explorer in SQL Server Management Studio, expand Integration Services Catalogs, right-click SSISDB, and then select Database Upgrade. 遵循 SSISDB 升級精靈 中的指示來升級資料庫。Follow the instructions in the SSISDB Upgrade Wizard to upgrade the database. 主要節點本機上啟動 [SSIDB 升級精靈]。Launch the SSIDB Upgrade Wizard locally on the primary node.

  4. 遵循步驟 2:將 SSISDB 新增至 Always On 可用性群組中的指示,將 SSISDB 新增回可用性群組。Follow the instructions in Step 2: Add SSISDB to an Always On Availability Group to add the SSISDB back to an availability group.

  5. 遵循步驟 3:啟用適用於 Always On 的 SSIS 支援中的指示進行。Follow the instructions in Step 3: Enable SSIS support for Always On.

相關內容Related Content