部署 Integration Services (SSIS) 项目和包Deploy Integration Services (SSIS) Projects and Packages

适用范围:SQL Server,包括 Linux Azure SQL 数据库yesAzure SQL 数据仓库no并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

Integration ServicesIntegration Services 支持两种部署模型:项目部署模型和旧的包部署模型。supports two deployment models, the project deployment model and the legacy package deployment model. 项目部署模型使您可以将项目部署到 Integration ServicesIntegration Services 服务器。The project deployment model enables you to deploy your projects to the Integration ServicesIntegration Services server.

有关早期包部署模型的详细信息,请参阅包部署 (SSIS)For more information about the legacy package deployment model, see Legacy Package Deployment (SSIS).

备注

项目部署模型是在 SQL Server 2012 Integration Services (SSIS)SQL Server 2012 Integration Services (SSIS)中引入的。The project deployment model was introduced in SQL Server 2012 Integration Services (SSIS)SQL Server 2012 Integration Services (SSIS). 使用此部署模型时,不能在未部署整个项目的情况下部署一个或多个包。With this deployment model, you were not able to deploy one or more packages without deploying the whole project. SQL Server 2016 Integration Services (SSIS)SQL Server 2016 Integration Services (SSIS) 引入的增量包部署功能能够让你部署一个或多个包,无需部署整个项目。introduced the Incremental Package Deployment feature, which lets you deploy one or more packages without deploying the whole project.

备注

本文介绍如何在一般情况下部署 SSIS 包以及如何在本地部署包。This article describes how to deploy SSIS packages in general, and how to deploy packages on premises. 还可将 SSIS 包部署到以下平台:You can also deploy SSIS packages to the following platforms:

比较项目部署模型和旧的包部署模型Compare Project Deployment Model and legacy Package Deployment Model

您为项目选择的部署模型的类型将决定可用于该项目的开发和管理选项。The type of deployment model that you choose for a project determines which development and administrative options are available for that project. 下表显示使用项目部署模型和使用包部署模型之间的差异和相似之处。The following table shows the differences and similarities between using the project deployment model and using the package deployment model.

在使用项目部署模型时When Using the Project Deployment Model 使用旧的包部署模型时When Using the legacy Package Deployment Model
项目是部署单元。A project is the unit of deployment. 包是部署单元。A package is the unit of deployment.
参数用于向包属性赋值。Parameters are used to assign values to package properties. 配置用于向包属性赋值。Configurations are used to assign values to package properties.
将包含包和参数的项目生成为一个项目部署文件(.ispac 扩展名)。A project, containing packages and parameters, is built to a project deployment file (.ispac extension). 包(.dtsx 扩展名)和配置(.dtsConfig 扩展名)单独保存到文件系统中。Packages (.dtsx extension) and configurations (.dtsConfig extension) are saved individually to the file system.
将包含包和参数的项目部署到 SQL Server 实例上的 SSISDB 目录中。A project, containing packages and parameters, is deployed to the SSISDB catalog on an instance of SQL Server. 包和配置复制到另一台计算机上的文件系统中。Packages and configurations are copied to the file system on another computer. 包也可以保存到 SQL Server 实例上的 MSDB 数据库中。Packages can also be saved to the MSDB database on an instance of SQL Server.
数据库引擎需要 CLR 集成。CLR integration is required on the database engine. 数据库引擎不需要 CLR 集成。CLR integration is not required on the database engine.
特定于环境的参数值存储于环境变量中。Environment-specific parameter values are stored in environment variables. 特定于环境的配置值存储于配置文件中。Environment-specific configuration values are stored in configuration files.
可在执行前在服务器上验证目录中的项目和包。Projects and packages in the catalog can be validated on the server before execution. 可以使用 SQL Server Management Studio、存储过程或托管代码执行该验证。You can use SQL Server Management Studio, stored procedures, or managed code to perform the validation. 恰好在执行之前对包进行验证。Packages are validated just before execution. 还可以使用 dtExec 或托管代码验证包。You can also validate a package with dtExec or managed code.
通过对数据库引擎启动执行,来执行包。Packages are executed by starting an execution on the database engine. 在开始执行前,将项目标识符、显式参数值(可选)和环境引用(可选)分配给某一执行。A project identifier, explicit parameter values (optional), and environment references (optional) are assigned to an execution before it is started.

还可以使用 dtExec执行包。You can also execute packages using dtExec.
使用 dtExecDTExecUI 执行实用工具执行包。Packages are executed using the dtExec and DTExecUI execution utilities. 适用配置是通过命令提示符参数(可选)来标识的。Applicable configurations are identified by command-prompt arguments (optional).
在执行过程中,包生成的事件将自动捕获并保存到目录中。During execution, events that are produced by the package are captured automatically and saved to the catalog. 您可以使用 TRANSACT-SQL 视图查询这些事件。You can query these events with Transact-SQL views. 在执行过程中,包生成的事件不自动捕获。During execution, events that are produced by a package are not captured automatically. 日志提供程序必须添加到包以便捕获事件。A log provider must be added to the package to capture events.
包在单独的 Windows 进程中运行。Packages are run in a separate Windows process. 包在单独的 Windows 进程中运行。Packages are run in a separate Windows process.
SQL Server 代理用于计划包执行。SQL Server Agent is used to schedule package execution. SQL Server 代理用于计划包执行。SQL Server Agent is used to schedule package execution.

项目部署模型的功能Features of Project Deployment Model

下表列出的功能仅可用于为项目部署模型开发的项目。The following table lists the features that are available to projects developed only for the project deployment model.

功能Feature 描述Description
ParametersParameters 参数指定包将使用的数据。A parameter specifies the data that will be used by a package. 您可以分别使用包参数和项目参数将参数范围限定在包级别或项目级别。You can scope parameters to the package level or project level with package parameters and project parameters, respectively. 参数可用于表达式或任务中。Parameters can be used in expressions or tasks. 在将项目部署到目录时,可为每个参数分配文字值,或者使用在设计时分配的默认值。When the project is deployed to the catalog, you can assign a literal value for each parameter or use the default value that was assigned at design time. 还可以引用环境变量来代替文字值。In place of a literal value, you can also reference an environment variable. 在包执行时解析环境变量值。Environment variable values are resolved at the time of package execution.
环境Environments 环境是可由 Integration ServicesIntegration Services 项目引用的变量的容器。An environment is a container of variables that can be referenced by Integration ServicesIntegration Services projects. 每个项目可以具有多个环境引用,但包执行的单个实例只能引用来自单个环境的变量。Each project can have multiple environment references, but a single instance of package execution can only reference variables from a single environment. 环境允许您对分配给包的值进行组织。Environments allow you to organize the values that you assign to a package. 例如,您可以具有名为“开发”、“测试”和“生产”的环境。For example, you might have environments named "Dev", "test", and "Production".
环境变量Environment variables 环境变量定义可在包执行过程中赋给参数的文字值。An environment variable defines a literal value that can be assigned to a parameter during package execution. 若要使用某一环境变量,请创建环境引用(在与具有参数的环境相对应的项目中),向该环境变量的名称分配某一参数值,并且在配置执行实例时指定相应的环境引用。To use an environment variable, create an environment reference (in the project that corresponds to the environment having the parameter), assign a parameter value to the name of the environment variable, and specify the corresponding environment reference when you configure an instance of execution.
SSISDB 目录SSISDB catalog 所有 Integration ServicesIntegration Services 对象都在某一 SQL Server 实例上称作 SSISDB 目录的数据库中进行存储和管理。All Integration ServicesIntegration Services objects are stored and managed on an instance of SQL Server in a database referred to as the SSISDB catalog. 通过该目录,您可以使用文件夹组织您的项目和环境。The catalog allows you to use folders to organize your projects and environments. 每个 SQL Server 实例可具有一个目录。Each instance of SQL Server can have one catalog. 每个目录中可具有零个或多个文件夹。Each catalog can have zero or more folders. 每个文件夹可具有零个或多个项目以及零个或多个环境。Each folder can have zero or more projects and zero or more environments. 该目录中的文件夹也可以用作针对 Integration ServicesIntegration Services 对象的权限的边界。A folder in the catalog can also be used as a boundary for permissions to Integration ServicesIntegration Services objects.
目录存储过程和视图Catalog stored procedures and views 可以使用大量存储过程和视图来管理该目录中的 Integration ServicesIntegration Services 对象。A large number of stored procedures and views can be used to manage Integration ServicesIntegration Services objects in the catalog. 例如,您可以指定参数和环境变量值,创建和启动执行,以及监视目录操作。For example, you can specify values to parameters and environment variables, create and start executions, and monitor catalog operations. 您甚至可以精确看到在执行开始前将由包使用的值。You can even see exactly which values will be used by a package before execution starts.

项目部署Project Deployment

项目部署模型的中心是项目部署文件(.ispac 扩展名)。At the center of the project deployment model is the project deployment file (.ispac extension). 该项目部署文件是自包含的部署单元,仅包含与项目中的包和参数有关的基本信息。The project deployment file is a self-contained unit of deployment that includes only the essential information about the packages and parameters in the project. 该项目部署文件不捕获在 Integration Services 项目文件(.dtproj 扩展名)中包含的所有信息。The project deployment file does not capture all of the information contained in the Integration Services project file (.dtproj extension). 例如,您用于撰写备注的附加文本文件不存储于该项目部署文件中,因此不部署到目录中。For example, additional text files that you use for writing notes are not stored in the project deployment file and thus are not deployed to the catalog.

部署 SSIS 项目和包时所需的权限Permissions Required to Deploy SSIS Projects and Packages

如果在默认情况下更改 SSIS 服务帐户,可能须向非默认服务账户授予其他权限,才能成功部署包。If you change the SSIS service account from the default, you may have to give additional permissions to the non-default service account before you can deploy packages successfully. 如果非默认服务帐户不具有所需权限,可能会收到以下错误消息。If the non-default service account doesn't have the required permissions, you may see the following error message.

A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal": System.ComponentModel.Win32Exception: A required privilege is not held by the client.

此错误通常是因为缺少 DCOM 权限造成的。This error is typically the result of missing DCOM permissions. 若要修复此错误,请执行以下操作:To fix the error, do the following:

  1. 打开“组件服务” 控制台(或运行 Dcomcnfg.exe)。Open the Component Services console (or run Dcomcnfg.exe).
  2. 在“组件服务” 控制台中,展开“组件服务” > “计算机” > “我的电脑” > “DCOM Config” 。In the Component Services console, expand Component Services > Computers > My Computer > DCOM Config.
  3. 在列表中,查找适用于所使用的 SQL Server 版本的 Microsoft SQL Server Integration Services xx.0 。In the list, locate Microsoft SQL Server Integration Services xx.0 for the version of SQL Server that you're using. 例如,SQL Server 2016 的版本为 13。For example, SQL Server 2016 is version 13.
  4. 右键单击并选择“属性” 。Right-click and select Properties.
  5. 在“Microsoft SQL Server Integration Services 13.0 属性” 对话框中,选择“安全性” 选项卡。In the Microsoft SQL Server Integration Services 13.0 Properties dialog box, select the Security tab.
  6. 针对三组权限中的每一组 — 启动与激活、访问和配置 — 选择“自定义” ,然后选择“编辑” 以打开“权限” 对话框。For each of the three sets of permissions - Launch and Activation, Access, and Configuration - select Customize, then select Edit to open the Permission dialog box.
  7. 在“权限” 对话框中,添加非默认服务帐户并根据需要授予“允许” 权限。In the Permission dialog box, add the non-default service account and grant Allow permissions as required. 通常来说,一个帐户具有“本地启动” 和“本地激活” 权限。Typically, an account has Local Launch and Local Activation permissions.
  8. 单击“确定” 两次,然后关闭“组件服务” 控制台。Click OK twice, then close the Component Services console.

有关本部分中描述的错误和 SSIS 服务帐户所需权限的详细信息,请参阅以下博客文章:For more info about the error described in this section and about the permissions required by the SSIS service account, see the following blog post:

Deploy Projects to Integration Services ServerDeploy Projects to Integration Services Server

Integration ServicesIntegration Services的当前版本中,您可以将您的项目部署到 Integration ServicesIntegration Services 服务器。In the current release of Integration ServicesIntegration Services, you can deploy your projects to the Integration ServicesIntegration Services server. 通过 Integration ServicesIntegration Services 服务器,您可以使用环境来管理包、运行包以及为包配置运行时值。The Integration ServicesIntegration Services server enables you to manage packages, run packages, and configure runtime values for packages by using environments.

备注

Integration ServicesIntegration Services的早期版本一样,在当前版本中,您也可以将您的包部署到 SQL Server 实例并且使用 Integration ServicesIntegration Services 服务运行和管理包。As in earlier versions of Integration ServicesIntegration Services, in the current release you can also deploy your packages to an instance of SQL Server and use Integration ServicesIntegration Services service to run and manage the packages. 您使用包部署模型。You use the package deployment model. 有关详细信息,请参阅早期包部署 (SSIS)For more information, see Legacy Package Deployment (SSIS).

若要将项目部署到 Integration ServicesIntegration Services 服务器,请完成以下任务:To deploy a project to the Integration ServicesIntegration Services server, complete the following tasks:

  1. 创建一个 SSISDB 目录(如果尚未创建)。Create an SSISDB catalog, if you haven't already. 有关详细信息,请参阅 SSIS 目录For more information, see SSIS Catalog.

  2. 通过运行“Integration Services 项目转换向导”可以将项目转换为项目部署模型 。Convert the project to the project deployment model by running the Integration Services Project Conversion Wizard. 有关详细信息,请参阅以下说明:将项目转换为项目部署模型For more information, see the following instructions: To convert a project to the project deployment model

    • 如果在 SQL Server 2014 Integration Services (SSIS)SQL Server 2014 Integration Services (SSIS) 中或更高版本中创建了项目,则默认情况下该项目使用项目部署模型。If you created the project in SQL Server 2014 Integration Services (SSIS)SQL Server 2014 Integration Services (SSIS) or later, by default the project uses the project deployment model.

    • 如果您在早期版本的 Integration ServicesIntegration Services中创建了项目,则在 Visual StudioVisual Studio中打开项目文件之后,将该项目转换为项目部署模型。If you created the project in an earlier release of Integration ServicesIntegration Services, after you open the project file in Visual StudioVisual Studio, convert the project to the project deployment model.

      备注

      如果项目包含一个或多个数据源,则在项目转换完成时删除数据源。If the project contains one or more datasources, the datasources are removed when the project conversion is completed. 若要创建到项目中的包可共享的数据源的连接,请在项目级别添加连接管理器。To create a connection to a data source that the packages in the project can share, add a connection manager at the project level. 有关详细信息,请参阅 在包中添加、删除或共享连接管理器For more information, see Add, Delete, or Share a Connection Manager in a Package.

      根据您是从 还是从 运行 Visual StudioVisual Studio “Integration Services 项目转换向导” SQL Server Management StudioSQL Server Management Studio,该向导将执行不同的转换任务。Depending on whether you run the Integration Services Project Conversion Wizard from Visual StudioVisual Studio or from SQL Server Management StudioSQL Server Management Studio, the wizard performs different conversion tasks.

      • 如果您是从 Visual StudioVisual Studio运行该向导的,则在项目中包含的包将从 Integration ServicesIntegration Services 2005、2008 或 2008 R2 转换为当前版本的 Integration ServicesIntegration Services使用的格式。If you run the wizard from Visual StudioVisual Studio, the packages contained in the project are converted from Integration ServicesIntegration Services 2005, 2008, or 2008 R2 to the format that is used by the current version of Integration ServicesIntegration Services. 原始项目 (.dtproj) 和包 (.dtsx) 文件将升级。The original project (.dtproj) and package (.dtsx) files are upgraded.

      • 如果你是从 SQL Server Management StudioSQL Server Management Studio运行该向导的,则该向导将根据项目中包含的包和配置生成一个项目部署文件 (.ispac)。If you run the wizard from SQL Server Management StudioSQL Server Management Studio, the wizard generates a project deployment file (.ispac) from the packages and configurations contained in the project. 原始包 (.dtsx) 文件不升级。The original package (.dtsx) files are not upgraded.

        您可以在该向导的 “选择目标” 页中选择一个现有文件或创建一个新文件。You can select an existing file or create a new file, in the Selection Destination page of the wizard.

        若要在转换项目时升级包文件,请从 运行 “Integration Services 项目转换向导” Visual StudioVisual StudioTo upgrade package files when a project is converted, run the Integration Services Project Conversion Wizard from Visual StudioVisual Studio. 若要单独从项目转换中升级包文件,请从 中运行 “Integration Services 项目转换向导” SQL Server Management StudioSQL Server Management Studio ,然后运行 “SSIS 包升级向导”To upgrade package files separately from a project conversion, run the Integration Services Project Conversion Wizard from SQL Server Management StudioSQL Server Management Studio and then run the SSIS Package Upgrade Wizard. 如果单独升级包文件,请确保您保存了这些更改。If you upgrade the package files separately, ensure that you save the changes. 否则,在您将项目转换为项目部署模型时,将不会转换对包的任何未保存的更改。Otherwise, when you convert the project to the project deployment model, any unsaved changes to the package are not converted.

    有关包升级的详细信息,请参阅 升级 Integration Services 包使用 SSIS 包升级向导升级 Integration Services 包For more information on package upgrade, see Upgrade Integration Services Packages and Upgrade Integration Services Packages Using the SSIS Package Upgrade Wizard.

  3. 将项目部署到 Integration ServicesIntegration Services 服务器。Deploy the project to the Integration ServicesIntegration Services server. 有关详细信息,请参阅下面的说明:将项目部署到 Integration Services 服务器For more information, see the instructions below: To deploy a project to the Integration Services Server.

  4. (可选)创建已部署项目的环境。(Optional) Create an environment for the deployed project.

将项目转换为项目部署模型To convert a project to the project deployment model

  1. Visual StudioVisual Studio 中打开该项目,然后在解决方案资源管理器中,右键单击该项目并单击“转换为项目部署模型” 。Open the project in Visual StudioVisual Studio, and then in Solution Explorer, right-click the project and click Convert to Project Deployment Model.

    -或 --or-

    Management StudioManagement Studio 的对象资源管理器中,右键单击“项目” 节点并选择“导入包” 。From Object Explorer in Management StudioManagement Studio, right-click the Projects node and select Import Packages.

  2. 完成向导。Complete the wizard.

将项目部署到 Integration Services 服务器To deploy a project to the Integration Services Server

  1. Visual StudioVisual Studio并打开项目,然后从 “项目” 菜单,选择 “部署” 以便启动 “Integration Services 部署向导”Open the project in Visual StudioVisual Studio, and then From the Project menu, select Deploy to launch the Integration Services Deployment Wizard.

    或多个or

    SQL Server Management StudioSQL Server Management Studio 的对象资源管理器中,展开 Integration ServicesIntegration Services > SSISDB 节点,并查找想要部署的项目的项目文件夹。In SQL Server Management StudioSQL Server Management Studio, expand the Integration ServicesIntegration Services > SSISDB node in Object Explorer, and locate the Projects folder for the project you want to deploy. 右键单击“项目” 文件夹,然后单击“部署项目” 。Right-click the Projects folder, and then click Deploy Project.

    或多个or

    在命令提示符下,从 %ProgramFiles%\Microsoft SQL Server\130\DTS\Binn 运行 isdeploymentwizard.exeFrom the command prompt, run isdeploymentwizard.exe from %ProgramFiles%\Microsoft SQL Server\130\DTS\Binn. 在 64 位计算机上, %ProgramFiles(x86)%\Microsoft SQL Server\130\DTS\Binn中还有 32 位版本的工具。On 64-bit computers, there is also a 32-bit version of the tool in %ProgramFiles(x86)%\Microsoft SQL Server\130\DTS\Binn.

  2. “选择源” 页上,单击 “项目部署文件” 以便选择项目的部署文件。On the Select Source page, click Project deployment file to select the deployment file for the project.

    或多个or

    单击 “Integration Services 目录” 以便选择已部署到 SSISDB 目录的项目。Click Integration Services catalog to select a project that has already been deployed to the SSISDB catalog.

  3. 完成向导。Complete the wizard.

将包部署到 Integration Services 服务器Deploy Packages to Integration Services Server

SQL Server 2016 Integration Services (SSIS)SQL Server 2016 Integration Services (SSIS) 引入了增量包部署功能,能够让你将一个或多个包部署到现有或新的项目,而无需部署整个项目。The Incremental Package Deployment feature introduced in SQL Server 2016 Integration Services (SSIS)SQL Server 2016 Integration Services (SSIS) lets you deploy one or more packages to an existing or new project without deploying the whole project.

通过使用 Integration Services 部署向导部署包Deploy packages by using the Integration Services Deployment Wizard

  1. 在命令提示符下,从 %ProgramFiles%\Microsoft SQL Server\130\DTS\Binn 运行 isdeploymentwizard.exeFrom the command prompt, run isdeploymentwizard.exe from %ProgramFiles%\Microsoft SQL Server\130\DTS\Binn. 在 64 位计算机上, %ProgramFiles(x86)%\Microsoft SQL Server\130\DTS\Binn 中还有 32 位版本的工具。On 64-bit computers, there is also a 32-bit version of the tool in %ProgramFiles(x86)%\Microsoft SQL Server\130\DTS\Binn.

  2. 在“选择源” 页上,切换到“包部署模型” 。On the Select Source page, switch to Package Deployment model. 然后,选择包含源包的文件夹,并配置包。Then, select the folder that contains source packages and configure the packages.

  3. 完成向导。Complete the wizard. 按照 Package Deployment Model中所述的后续步骤进行操作。Follow the remaining steps described in Package Deployment Model.

使用 SQL Server Management Studio 部署包Deploy packages by using SQL Server Management Studio

  1. 在 SQL Server Management Studio 中,展开对象资源管理器中的 “Integration Services 目录” > SSISDB节点。In SQL Server Management Studio, expand the Integration Services Catalogs > SSISDB node in Object Explorer.

  2. 右键单击“项目”文件夹,然后单击“部署项目”。 Right-click the Projects folder, and then click Deploy Projects.

  3. 如果看到“简介” 页,单击“下一步” 以继续。If you see the Introduction page, click Next to continue.

  4. 在“选择源” 页上,切换到“包部署模型” 。On the Select Source page, switch to Package Deployment model. 然后,选择包含源包的文件夹,并配置包。Then, select the folder that contains source packages and configure the packages.

  5. 完成向导。Complete the wizard. 按照 Package Deployment Model中所述的后续步骤进行操作。Follow the remaining steps described in Package Deployment Model.

使用 SQL Server Data Tools (Visual Studio) 部署包Deploy packages by using SQL Server Data Tools (Visual Studio)

  1. 在 Visual Studio 中,在 Integration Services 项目处于打开状态时,选择一个或多个要部署的包。In Visual Studio, with an Integration Services project open, select the package or packages that you want to deploy.

  2. 右键单击并选择“部署包”。 Right-click and select Deploy Package. 部署向导将打开,并且所选的包已配置为源包。The Deployment Wizard opens with the selected packages configured as the source packages.

  3. 完成向导。Complete the wizard. 按照 Package Deployment Model中所述的后续步骤进行操作。Follow the remaining steps described in Package Deployment Model.

使用 deploy_packages 存储过程部署包Deploy packages by using the deploy_packages stored procedure

可以使用 [catalog].[deploy_packages] 存储过程将一个或多个 SSIS 包部署到 SSIS 目录。You can use the [catalog].[deploy_packages] stored procedure to deploy one or more SSIS packages to the SSIS Catalog. 下面的代码示例演示如何通过此存储过程将包部署到 SSIS 服务器。The following code example demonstrates the use of this stored procedure to deploy packages to an SSIS server. 有关详细信息,请参阅 catalog.deploy_packagesFor more info, see catalog.deploy_packages.

  
private static void Main(string[] args)  
{  
    // Connection string to SSISDB  
    var connectionString = "Data Source=.;Initial Catalog=SSISDB;Integrated Security=True;MultipleActiveResultSets=false";  
  
    using (var sqlConnection = new SqlConnection(connectionString))  
    {  
        sqlConnection.Open();  
  
        var sqlCommand = new SqlCommand  
        {  
            Connection = sqlConnection,  
            CommandType = CommandType.StoredProcedure,  
            CommandText = "[catalog].[deploy_packages]"  
        };  
  
        var packageData = Encoding.UTF8.GetBytes(File.ReadAllText(@"C:\Test\Package.dtsx"));  
  
        // DataTable: name is the package name without extension and package_data is byte array of package.  
        var packageTable = new DataTable();  
        packageTable.Columns.Add("name", typeof(string));  
        packageTable.Columns.Add("package_data", typeof(byte[]));  
        packageTable.Rows.Add("Package", packageData);  
  
        // Set the destination project and folder which is named Folder and Project.  
        sqlCommand.Parameters.Add(new SqlParameter("@folder_name", SqlDbType.NVarChar, ParameterDirection.Input, "Folder", -1));  
        sqlCommand.Parameters.Add(new SqlParameter("@project_name", SqlDbType.NVarChar, ParameterDirection.Input, "Project", -1));  
        sqlCommand.Parameters.Add(new SqlParameter("@packages_table", SqlDbType.Structured, ParameterDirection.Input, packageTable, -1));  
  
        var result = sqlCommand.Parameters.Add("RetVal", SqlDbType.Int);  
        result.Direction = ParameterDirection.ReturnValue;  
  
        sqlCommand.ExecuteNonQuery();  
    }  
}  
  

使用管理对象模型 API 部署包Deploy packages using the Management Object Model API

下面的代码示例演示如何通过管理对象模型 API 将包部署到服务器。The following code example demonstrates the use of the Management Object Model API to deploy packages to server.

  
static void Main()  
 {  
     // Before deploying packages, make sure the destination project exists in SSISDB.  
     var connectionString = "Data Source=.;Integrated Security=True;MultipleActiveResultSets=false";  
     var catalogName = "SSISDB";  
     var folderName = "Folder";  
     var projectName = "Project";  
  
     // Get the folder instance.  
     var sqlConnection = new SqlConnection(connectionString);  
     var store = new Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices(sqlConnection);  
     var folder = store.Catalogs[catalogName].Folders[folderName];  
  
     // Key is package name without extension and value is package binaries.  
     var packageDict = new Dictionary<string, string>();  
  
     var packageData = File.ReadAllText(@"C:\Folder\Package.dtsx");  
     packageDict.Add("Package", packageData);  
  
     // Deploy package to the destination project.  
     folder.DeployPackages(projectName, packageDict);  
 }  
  

“转换为包部署模型”对话框Convert to Package Deployment Model Dialog Box

使用 “转换为包部署模型” 命令可以在检查项目和项目中的每个包与包部署模型是否兼容后将包转换为该模型。The Convert to Package Deployment Model command allows you to convert a package to the package deployment model after checking the project and each package in the project for compatibility with that model. 如果包使用项目部署模型特有的功能(如参数),则不能转换该包。If a package uses features unique to the project deployment model, such as parameters, then the package cannot be converted.

将包转换为包部署模型需要两个步骤。Converting a package to the package deployment model requires two steps.

  1. “项目” 菜单选择 “转换为包部署模型” 命令时,检查项目和其中的每个包是否与此模型兼容。When you select the Convert to Package Deployment Model command from the Project menu, the project and each package are checked for compatibility with this model. 结果显示在 “结果” 表中。The results are displayed in the Results table.

    如果项目或其中的某个包未通过兼容测试,单击 “结果” 列中的 “失败” 可以获取详细信息。If the project or a package fails the compatibility test, click Failed in the Result column for more information. 单击 “保存报告” ,以将此信息的副本保存到文本文件。Click Save Report to save a copy of this information to a text file.

  2. 如果项目和所有包都通过了兼容测试,则单击 “确定” 以转换包。If the project and all packages pass the compatibility test, then click OK to convert the package.

备注

若要将项目转换为项目部署模型,请使用 “Integration Services 项目转换向导”To convert a project to the project deployment model, use the Integration Services Project Conversion Wizard. 有关详细信息,请参阅 Integration Services Project Conversion WizardFor more information, see Integration Services Project Conversion Wizard.

Integration Services 部署向导Integration Services Deployment Wizard

Integration Services 部署向导 支持两种部署模型:The Integration Services Deployment Wizard supports two deployment models:

  • 项目部署模型Project deployment model
  • 包部署模型Package deployment model

项目部署模型 允许将 SQL Server Integration Services (SSIS) 项目作为一个单元部署到 SSIS 目录中。The Project Deployment model allows you to deploy a SQL Server Integration Services (SSIS) project as a single unit to the SSIS Catalog.

包部署模型 允许将已更新的包部署到 SSIS 目录中,而无需部署整个项目。The Package Deployment model allows you to deploy packages that you have updated to the SSIS Catalog without having to deploy the whole project.

备注

向导默认部署是项目部署模型。The Wizard default deployment is the Project Deployment model.

启动向导Launch the wizard

通过以下方式之一启动向导:Launch the wizard by either:

  • 在 Windows Search 中键入 “SQL Server 部署向导”Typing "SQL Server Deployment Wizard" in Windows Search

或多个or

  • 在 SQL Server 安装文件夹下搜索可执行文件 ISDeploymentWizard.exe;例如 :“C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn”。Search for the executable file ISDeploymentWizard.exe under the SQL Server installation folder; for example: "C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn".

注意: 在“简介” 页上单击“下一步” ,切换到“选择源” 页。NOTE: If you see the Introduction page, click Next to switch to the Select Source page.

对于每个部署模型,此页上的设置会有所不同。The settings on this page are different for each deployment model. 基于你在此页中选择的模型,按照 Project Deployment Model 部分或 Package Deployment Model 部分的步骤进行操作。Follow steps in the Project Deployment Model section or Package Deployment Model section based on the model you selected in this page.

Project Deployment ModelProject Deployment Model

选择源Select Source

若要部署你所创建的项目部署文件,请选择“项目部署文件” 并输入 .ispac 文件的路径。To deploy a project deployment file that you created, select Project deployment file and enter the path to the .ispac file. 若要部署位于 Integration ServicesIntegration Services 目录中的项目,请选择 “Integration Services 目录” ,然后输入目录中指向该项目的服务器名称和路径。To deploy a project that resides in the Integration ServicesIntegration Services catalog, select Integration Services catalog, and then enter the server name and the path to the project in the catalog. 单击“下一步” 以查看“选择目标” 页。Click Next to see the Select Destination page.

选择目标Select Destination

若要在 Integration ServicesIntegration Services 目录中选择项目的目标文件夹,请输入 SQL ServerSQL Server 实例或单击 “浏览” 从服务器列表中选择。To select the destination folder for the project in the Integration ServicesIntegration Services catalog, enter the SQL ServerSQL Server instance or click Browse to select from a list of servers. 然后输入 SSISDB 中的项目路径或单击 “浏览” 以选择此路径。Enter the project path in SSISDB or click Browse to select it. 单击“下一步” 以查看“检查” 页。Click Next to see the Review page.

查看(和部署)Review (and deploy)

该页使你能够查看你所选择的设置。The page allows you to review the settings you have selected. 可以通过单击 “上一步” 或单击左窗格中的任意步骤来更改所做的选择。You can change your selections by clicking Previous, or by clicking any of the steps in the left pane. 单击“部署” 以启动部署过程。Click Deploy to start the deployment process.

结果Results

部署过程完成之后,将看到“结果” 页。After the deployment process is complete, you should see the Results page. 该页显示每个操作是成功了还是失败了。This page displays the success or failure of each action. 如果操作失败,单击 “结果” 列中的 “失败” 可以显示错误说明。If the action fails, click the Failed in the Result column to display an explanation of the error. 单击“保存报表...” 以将结果保存到 XML 文件,或单击“关闭” 以退出向导。Click Save Report... to save the results to an XML file or Click Close to exit the wizard.

Package Deployment ModelPackage Deployment Model

选择源Select Source

在“部署模型” 中选择“包部署” 选项后,“Integration Services 部署向导” 中的“选择源” 页会显示包部署模型的特定设置。The Select Source page in the Integration Services Deployment Wizard shows settings specific to the package deployment model when you selected the Package Deployment option for the deployment model.

若要选择源包,请单击“浏览…”按钮以选择包含包的“文件夹”,或在“包文件夹路径”文本框中键入文件夹路径,然后单击页面底部的“刷新”按钮 。To select the source packages, click the Browse... button to select the folder that contains the packages or type the folder path in the Packages folder path textbox and click Refresh button at the bottom of the page. 现在,将可以在列表框中看见特定文件夹中的所有包。Now, you should see all the packages in the specified folder in the list box. 默认情况下,所有的包都会被选中。By default, all the packages are selected. 单击第一列中的“复选框” 以选择要部署到服务器的包。Click the checkbox in the first column to choose which packages you want to be deployed to server.

请参阅“状态” 和“消息” 列以验证包的状态。Refer to the Status and Message columns to verify the status of package. 如果将状态设置为“就绪” 或“警告” ,部署向导则不会妨碍部署过程。If the status is set to Ready or Warning, the deployment wizard would not block the deployment process. 如果状态设置为“错误” ,则向导将不会继续部署所选的包。If the status is set to Error, the wizard wouldn't proceed to deploy the selected packages. 若要查看详细的警告或错误消息,请单击“消息”列中的链接 。To view the detailed Warning or Error messages, click the link in the Message column.

如果使用密码加密敏感数据或包数据,请在“密码” 列中键入密码,然后单击“刷新” 按钮以验证是否接受此密码。If the sensitive data or package data are encrypted with a password, type the password in the Password column and click the Refresh button to verify whether the password is accepted. 如果密码正确无误,状态将会更改为“就绪” ,而且警告消息将会消失。If the password is correct, the status would change to Ready and the warning message will disappear. 如果有多个包具有相同的密码,请选择具有相同加密密码的包,在“密码”文本框中输入密码,选择“应用”按钮 。If there are multiple packages with the same password, select the packages with the same encryption password, type the password in the Password textbox and select the Apply button. 该密码将应用到所选包。The password would be applied to the selected packages.

如果所有选定包的状态未设置为“错误” ,则可使用“下一步” 按钮以继续进行包部署过程。If the status of all the selected packages is not set to Error, the Next button will be enabled so that you can continue with the package deployment process.

选择目标Select Destination

在选择包源后,单击“下一步” 按钮以切换到“选择目标” 页。After selecting package sources, click the Next button to switch to the Select Destination page. 必须将包部署到 SSIS 目录 (SSISDB) 中的项目。Packages must be deployed to a project in the SSIS Catalog (SSISDB). 在部署包之前,请确保 SSIS 目录中已存在该目标项目。Before deploying packages, ensure the destination project already exists in the SSIS Catalog. 如果项目不存在,请创建一个空项目。Create an empty project if a project does not exist. 在“选择目标”页中的“服务器名称”文本框中键入服务器名称,或单击“浏览…”按钮以选择服务器实例 。In the Select Destination page, type the server name in the Server Name textbox or click the Browse... button to select a server instance. 然后单击“路径”文本框旁的“浏览…”按钮以指定目标项目 。Then click the Browse... button next to the Path textbox to specify the destination project. 如果项目不存在,请单击“新建项目…”按钮以创建空项目作为目标项目 。If the project does not exist, click the New project... button to create an empty project as the destination project. 必须在文件夹下创建项目。The project must be created under a folder.

查看和部署Review and deploy

在“选择目标” 页上单击“下一步” 以切换到“Integration Services 部署向导” 中的“审阅” 页。Click Next on the Select Destination page to switch to the Review page in the Integration Services Deployment Wizard. 在审阅页中,审阅有关部署操作的摘要报表。In the review page, review the summary report about the deployment action. 验证之后,单击“部署” 按钮以执行部署操作。After the verification, click the Deploy button to perform the deployment action.

结果Results

部署完成之后,将看到“结果” 页。After the deployment is complete, you should see the Results page. 在“结果” 页中,查看部署过程中每个步骤的结果。On the Results page, review results from each step in the deployment process. 单击“保存报表”以保存部署报表,或单击“关闭”以关闭该向导 。Click Save Report to save the deployment report or Close to the close the wizard.

创建和映射服务器环境Create and Map a Server Environment

创建服务器环境来指定已部署到 Integration ServicesIntegration Services 服务器的项目中所含包的运行时值。You create a server environment to specify runtime values for packages contained in a project you've deployed to the Integration ServicesIntegration Services server. 您可以随后针对特定包、入口点包或给定项目中的所有包,将环境变量映射到参数。You can then map the environment variables to parameters, for a specific package, for entry-point packages, or for all the packages in a given project. 入口点包通常是执行子包的父包。An entry-point package is typically a parent package that executes a child package.

重要

对于给定的执行,只能使用单个服务器环境中包含的值来执行包。For a given execution, a package can execute only with the values contained in a single server environment.

您可以查询视图以获得服务器环境、环境引用和环境变量的列表。You can query views for a list of server environments, environment references, and environment variables. 你也可以调用存储过程来添加、删除和修改环境、环境引用和环境变量。You can also call stored procedures to add, delete, and modify environments, environment references, and environment variables. 有关详细信息,请参阅 SSIS Catalog 中的“服务器环境、服务器变量和服务器环境引用” 一节。For more information, see the Server Environments, Server Variables, and Server Environment References section in SSIS Catalog.

创建和使用服务器环境To create and use a server environment

  1. Management StudioManagement Studio 中,在对象资源管理器中依次展开“Integration ServicesIntegration Services 目录”> SSISDB 节点,找到要为其创建环境的项目的“环境”文件夹 。In Management StudioManagement Studio, expand the Integration ServicesIntegration Services Catalogs SSISDB node in Object Explorer, and locate the Environments folder of the project for which you want to create an environment.

  2. 右键单击“环境” 文件夹,然后单击“创建环境” 。Right-click the Environments folder, and then click Create Environment.

  3. 为环境键入名称,并添加说明(可选)。Type a name for the environment and optionally add a description. 单击“确定” 。Click OK.

  4. 右键单击该新环境,然后单击“属性” 。Right-click the new environment and then click Properties.

  5. “变量” 页上,执行以下操作以便添加变量。On the Variables page, do the following to add a variable.

    1. 选择变量的 “类型”Select the Type for the variable. 变量的名称无需匹配你将映射到该变量的项目参数的名称。The name of the variable does not need to match the name of the project parameter that you map to the variable.

    2. 输入变量的可选 “说明”Enter an optional Description for the variable.

    3. 输入环境变量的 “值”Enter the Value for the environment variable.

      有关环境变量名称的规则的信息,请参阅 SSIS Catalog 中的“环境变量” 一节。For information about the rules for environment variable names, see the Environment Variable section in SSIS Catalog.

    4. 通过选中或取消选中 “敏感” 复选框,指示该变量是否包含敏感值。Indicate whether the variable contains sensitive value, by selecting or clearing the Sensitive checkbox.

      如果您选择 “敏感” ,则变量值不会显示在 “值” 字段中。If you select Sensitive, the variable value does not display in the Value field.

      敏感值在 SSISDB 目录中进行加密。Sensitive values are encrypted in the SSISDB catalog. 有关加密的详细信息,请参阅 SSIS CatalogFor more information about the encryption, see SSIS Catalog.

  6. “权限” 页上,通过执行以下操作,授予或拒绝所选用户和角色的权限。On the Permissions page, grant or deny permissions for selected users and roles by doing the following.

    1. 单击 “浏览” ,然后在 “浏览所有主体” 对话框中选择一个或多个用户和角色。Click Browse, and then select one or more users and roles in the Browse All Principals dialog box.

    2. “登录名或角色” 区域中,选择您要为其授予或拒绝权限的用户或角色。In the Logins or roles area, select the user or role that you want to grant or deny permissions for.

    3. 在“显式”区域中,选择每个权限旁边的“授予”或“拒绝” 。In the Explicit area, select Grant or Deny next to each permission.

  7. 若要编写环境的脚本,请单击 “脚本”To script the environment, click Script. 默认情况下,该脚本显示在一个新的查询编辑器窗口中。By default, the script displays in a new Query Editor window.

    提示

    对环境属性进行了更改(例如添加变量)后,并且在“环境属性”对话框中单击“确定”前,需要单击“脚本” 。You need to click Script after you've made one or changes to the environment properties, such as adding a variable, and before you click OK in the Environment Properties dialog box. 否则,将不会生成脚本。Otherwise, a script is not generated.

  8. 单击 “确定” 保存对环境属性的更改。Click OK to save your changes to the environment properties.

  9. 在对象资源管理器的 SSISDB 节点下,展开“项目” 文件夹,右键单击项目,然后单击“配置” 。Under the SSISDB node in Object Explorer, expand the Projects folder, right-click the project, and then click Configure.

  10. “引用” 页上,单击 “添加” 以便添加一个环境,然后单击 “确定” 以便保存对该环境的引用。On the References page, click Add to add an environment, and then click OK to save the reference to the environment.

  11. 再次右键单击该项目,然后单击“配置” 。Right-click the project again, and then click Configure.

  12. 若要将环境变量映射到在设计时添加到包的参数,或映射到将 Integration ServicesIntegration Services 项目转换为项目部署模型时生成的参数,请执行以下操作:To map the environment variable to a parameter that you added to the package at design-time or to a parameter that was generated when you converted the Integration ServicesIntegration Services project to the project deployment model, do the following:

    1. “参数” 页上的 “参数” 选项卡中,单击 “值” 字段旁边的浏览按钮。In the Parameters tab on the Parameters page, click the browse button next to the Value field.

    2. 单击 “使用环境变量” ,然后选择已创建的环境变量。Click Use environment variable, and then select the environment variable you created.

  13. 若要将环境变量映射到连接管理器属性,请执行以下操作。To map the environment variable to a connection manager property, do the following. 将在 SSIS 服务器上为连接管理器属性自动生成参数。Parameters are automatically generated on the SSIS server for the connection manager properties.

    1. 在“参数”页上的“连接管理器”选项卡中,单击“值”字段旁边的浏览按钮 。In the Connection Managers tab on the Parameters page, click the browse button next to the Value field.

    2. 单击 “使用环境变量” ,然后选择已创建的环境变量。Click Use environment variable, and then select the environment variable you created.

  14. 单击 “确定” 两次以保存所做的更改。Click OK twice to save your changes.

使用存储过程部署和执行 SSIS 包Deploy and Execute SSIS Packages using Stored Procedures

在您配置一个 Integration ServicesIntegration Services 项目以便使用项目部署模型时,可以使用 SSISSSIS 目录中的存储过程部署该项目并且执行包。When you configure an Integration ServicesIntegration Services project to use the project deployment model, you can use stored procedures in the SSISSSIS catalog to deploy the project and execute the packages. 有关项目部署模型的信息,请参阅 Deployment of Projects and PackagesFor information about the project deployment model, see Deployment of Projects and Packages.

您还可以使用 SQL Server Management StudioSQL Server Management StudioSQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 部署项目和执行包。You can also use SQL Server Management StudioSQL Server Management Studio or SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) to deploy the project and execute the packages. 有关详细信息,请参阅“另请参见” 部分中的主题。For more information, see the topics in the See Also section.

提示

您可以通过执行以下操作为下面的过程中列出的存储过程轻松地生成 Transact-SQL 语句,但 catalog.deploy_project 除外:You can easily generate the Transact-SQL statements for the stored procedures listed in the procedure below, with the exception of catalog.deploy_project, by doing the following:

  1. SQL Server Management StudioSQL Server Management Studio中,在对象资源管理器中展开“Integration Services 目录” 节点,然后导航到您要执行的包。In SQL Server Management StudioSQL Server Management Studio, expand the Integration Services Catalogs node in Object Explorer and navigate to the package you want to execute.

  2. 右键单击该包,然后单击“执行” 。Right-click the package, and then click Execute.

  3. 根据需要,设置参数值、连接管理器属性和 “高级” 选项卡中的选项(例如,日志记录级别)。As needed, set parameters values, connection manager properties, and options in the Advanced tab such as the logging level.

    有关日志记录级别的详细信息,请参阅 启用日志记录在 SSIS 服务器上的包执行的For more information about logging levels, see Enable Logging for Package Execution on the SSIS Server.

  4. 在单击 “确定” 以便执行该包之前,单击 “脚本”Before clicking OK to execute the package, click Script. Transact-SQL 将出现在 SQL Server Management StudioSQL Server Management Studio的“查询编辑器”窗口中。The Transact-SQL appears in a Query Editor window in SQL Server Management StudioSQL Server Management Studio.

使用存储过程部署和执行包To deploy and execute a package using stored procedures

  1. 调用 catalog.deploy_project(SSISDB 数据库) 将包含包的 Integration ServicesIntegration Services 项目部署到 Integration ServicesIntegration Services 服务器。Call catalog.deploy_project (SSISDB Database) to deploy the Integration ServicesIntegration Services project that contains the package to the Integration ServicesIntegration Services server.

    若要检索 Integration ServicesIntegration Services 项目部署文件的二进制内容,对于 @project_stream 参数,请将 SELECT 语句与 OPENROWSET 函数和 BULK 行集提供程序一起使用。To retrieve the binary contents of the Integration ServicesIntegration Services project deployment file, for the @project_stream parameter_, use a SELECT statement with the OPENROWSET function and the BULK rowset provider. 通过 BULK 行集提供程序,您可以从文件读取数据。The BULK rowset provider enables you to read data from a file. BULK 行集提供程序的 SINGLE_BLOB 参数将该数据文件的内容以 varbinary(max) 类型的单行、单列行集的形式返回。The SINGLE_BLOB argument for the BULK rowset provider returns the contents of the data file as a single-row, single-column rowset of type varbinary(max). 有关详细信息,请参阅 OPENROWSET (Transact-SQL)For more information, see OPENROWSET (Transact-SQL).

    在下面的示例中,SSISPackages_ProjectDeployment 项目将部署到 Integration ServicesIntegration Services 服务器上的“SSIS 包”文件夹。In the following example, the SSISPackages_ProjectDeployment project is deployed to the SSIS Packages folder on the Integration ServicesIntegration Services server. 二进制数据从项目文件 (SSISPackage_ProjectDeployment.ispac) 读取并且存储于 varbinary(max) 类型的 _@ProjectBinary 参数中。The binary data is read from the project file (SSISPackage_ProjectDeployment.ispac) and is stored in the _@ProjectBinary parameter of type varbinary(max). 将 @ProjectBinary 参数值赋给 @project_stream 参数 。The @ProjectBinary parameter value is assigned to the @project_stream parameter.

    DECLARE @ProjectBinary as varbinary(max)  
    DECLARE @operation_id as bigint  
    Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)  
    
    Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out  
    
    
  2. 调用 catalog.create_execution(SSISDB 数据库) 来创建包执行的实例,并根据需要调用 catalog.set_execution_parameter_value(SSISDB 数据库) 来设置运行时参数值。Call catalog.create_execution (SSISDB Database) to create an instance of the package execution, and optionally call catalog.set_execution_parameter_value (SSISDB Database) to set runtime parameter values.

    在下面的示例中,catalog.create_execution 为在 SSISPackage_ProjectDeployment 项目中包含的 package.dtsx 创建一个执行实例。In the following example, catalog.create_execution creates an instance of execution for package.dtsx that is contained in the SSISPackage_ProjectDeployment project. 该项目位于“SSIS 包”文件夹中。The project is located in the SSIS Packages folder. 存储过程返回的 execution_id 用于对 catalog.set_execution_parameter_value 的调用中。The execution_id returned by the stored procedure is used in the call to catalog.set_execution_parameter_value. 此第二个存储过程将 LOGGING_LEVEL 参数设置为 3(详细日志记录),并且将名为 Parameter1 的包参数设置为值 1。This second stored procedure sets the LOGGING_LEVEL parameter to 3 (verbose logging) and sets a package parameter named Parameter1 to a value of 1.

    对于 LOGGING_LEVEL 之类的参数,object_type 值为 50。For parameters such as LOGGING_LEVEL the object_type value is 50. 对于包参数,object_type 值为 30。For package parameters the object_type value is 30.

    Declare @execution_id bigint  
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SSISPackage_ProjectDeployment', @use32bitruntime=False, @reference_id=1  
    
    Select @execution_id  
    DECLARE @var0 smallint = 3  
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0  
    
    DECLARE @var1 int = 1  
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var1  
    
    GO  
    
    
  3. 调用 catalog.start_execution(SSISDB 数据库)执行包。Call catalog.start_execution (SSISDB Database) to execute the package.

    在下面的示例中,将对 catalog.start_execution 的调用添加到 Transact-SQL 以便开始包执行。In the following example, a call to catalog.start_execution is added to the Transact-SQL to start the package execution. 将使用 catalog.create_execution 存储过程返回的 execution_id。The execution_id returned by the catalog.create_execution stored procedure is used.

    Declare @execution_id bigint  
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SSISPackage_ProjectDeployment', @use32bitruntime=False, @reference_id=1  
    
    Select @execution_id  
    DECLARE @var0 smallint = 3  
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0  
    
    DECLARE @var1 int = 1  
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var1  
    
    EXEC [SSISDB].[catalog].[start_execution] @execution_id  
    GO  
    
    

使用存储过程将项目从一个服务器部署到另一个服务器To deploy a project from server to server using stored procedures

可以通过使用 catalog.get_project(SSISDB 数据库)catalog.deploy_project(SSISDB 数据库)存储过程在服务器之间部署项目。You can deploy a project from server to server by using the catalog.get_project (SSISDB Database) and catalog.deploy_project (SSISDB Database) stored procedures.

在运行存储过程之前,您需要执行以下操作。You need to do the following before running the stored procedures.

  • 创建一个链接服务器对象。Create a linked server object. 有关详细信息,请参阅创建链接服务器(SQL Server 数据库引擎)For more information, see Create Linked Servers (SQL Server Database Engine).

    “链接服务器属性”“服务器选项” 页上,将 RPCRPC Out 设置为 TrueOn the Server Options page of the Linked Server Properties dialog box, set RPC and RPC Out to True. 此外,将 “为 RPC 启用针对分布式事务的升级” 设置为 FalseAlso, set Enable Promotion of Distributed Transactions for RPC to False.

  • 通过在对象资源管理器中展开“链接服务器”下的“提供程序”节点,右键单击该提供程序,然后单击“属性”,对为链接服务器选择的提供程序启用动态参数。 Enable dynamic parameters for the provider you selected for the linked server, by expanding the Providers node under Linked Servers in Object Explorer, right-clicking the provider, and then clicking Properties. 选择 “动态参数” 旁边的 “启用”Select Enable next to Dynamic parameter.

  • 确认分布式事务处理协调器 (DTC) 在两个服务器上均启动。Confirm that the Distributed Transaction Coordinator (DTC) is started on both servers.

调用 catalog.get_project 以便返回该项目的二进制文件,然后调用 catalog.deploy_project。Call catalog.get_project to return the binary for the project, and then call catalog.deploy_project. 将 catalog.get_project 返回的值插入 varbinary(max) 类型的表变量中。The value returned by catalog.get_project is inserted into a table variable of type varbinary(max). 链接服务器无法返回类型为 varbinary(max) 的结果。The linked server can't return results that are varbinary(max).

在下面的示例中,catalog.get_project 为链接服务器上的 SSISPackages 项目返回二进制文件。In the following example, catalog.get_project returns a binary for the SSISPackages project on the linked server. catalog.deploy_project 将该项目部署到本地服务器上名为 DestFolder 的文件夹中。The catalog.deploy_project deploys the project to the local server, to the folder named DestFolder.

declare @resultsTableVar table (  
project_binary varbinary(max)  
)  
  
INSERT @resultsTableVar (project_binary)  
EXECUTE [MyLinkedServer].[SSISDB].[catalog].[get_project] 'Packages', 'SSISPackages'  
  
declare @project_binary varbinary(max)  
select @project_binary = project_binary from @resultsTableVar  
  
exec [SSISDB].[CATALOG].[deploy_project] 'DestFolder', 'SSISPackages', @project_binary  
  

Integration Services 项目转换向导Integration Services Project Conversion Wizard

“Integration Services 项目转换向导” 可以将项目转换为项目部署模型。The Integration Services Project Conversion Wizard converts a project to the project deployment model.

备注

如果项目包含一个或多个数据源,则在项目转换完成时删除数据源。If the project contains one or more datasources, the datasources are removed when the project conversion is completed. 若要创建到可由项目中的包共享的数据源的连接,请在项目级别添加连接管理器。To create a connection to a data source that can be shared by the packages in the project, add a connection manager at the project level. 有关详细信息,请参阅 在包中添加、删除或共享连接管理器For more information, see Add, Delete, or Share a Connection Manager in a Package.

您希望做什么?What do you want to do?

打开“Integration Services 项目转换向导”Open the Integration Services Project Conversion Wizard

执行下列操作之一以打开 “Integration Services 项目转换” 向导。Do one of the following to open the Integration Services Project Conversion Wizard.

  • Visual StudioVisual Studio 中打开该项目,然后在解决方案资源管理器中,右键单击该项目并单击“转换为项目部署模型” 。Open the project in Visual StudioVisual Studio, and then in Solution Explorer, right-click the project and click Convert to Project Deployment Model.

  • Management StudioManagement Studio 的对象资源管理器中,右键单击“Integration Services 目录”中的“项目”节点,然后选择“导入包” 。From Object Explorer in Management StudioManagement Studio, right-click the Projects node in the Integration Services Catalog and select Import Packages.

根据您是从 还是从 运行 Visual StudioVisual Studio “Integration Services 项目转换向导” SQL Server Management StudioSQL Server Management Studio,该向导将执行不同的转换任务。Depending on whether you run the Integration Services Project Conversion Wizard from Visual StudioVisual Studio or from SQL Server Management StudioSQL Server Management Studio, the wizard performs different conversion tasks.

设置“查找包”页上的选项Set Options on the Locate Packages Page

备注

只有在从 Management StudioManagement Studio运行该向导时,“查找包” 页才可用。The Locate Packages page is available only when you run the wizard from Management StudioManagement Studio.

在“源”下拉列表中选择“文件系统”时,该页显示以下选项 。The following option displays on the page when you select File system in the Source drop-down list. 当包驻留在文件系统中时选择此选项。Select this option when the package is resides in the file system.

文件夹Folder
键入包路径,或通过单击“浏览” 导航到该包。Type the package path, or navigate to the package by clicking Browse.

在“源”下拉列表中选择“SSIS 包存储区”时,该页显示以下选项 。The following options display on the page when you select SSIS Package Store in the Source drop-down list. 有关包存储区的详细信息,请参阅包管理(SSIS 服务)For more information about the package store, see Package Management (SSIS Service).

ServerServer
键入服务器名称或选择该服务器。Type the server name or select the server.

文件夹Folder
键入包路径,或通过单击“浏览” 导航到该包。Type the package path, or navigate to the package by clicking Browse.

在“源”下拉列表中选择“Microsoft SQL Server”时,该页显示以下选项 。The following options display on the page when you select Microsoft SQL Server in the Source drop-down list. 当包驻留在 Microsoft SQL ServerSQL Server中时选择此选项。Select this option when the package resides in Microsoft SQL ServerSQL Server.

ServerServer
键入服务器名称或选择该服务器。Type the server name or select the server.

使用 Windows 身份验证Use Windows authentication
Microsoft Windows 身份验证模式允许用户通过 Windows 用户帐户进行连接。Microsoft Windows Authentication mode allows a user to connect through a Windows user account. 如果使用 Windows 身份验证,则不需要提供用户名或密码。If you use Windows Authentication, you do not need to provide a user name or password.

使用 SQL Server 身份验证Use SQL Server authentication
当户使用指定的登录名和密码从不可信连接进行连接时, SQL ServerSQL Server 将通过检查是否已设置 SQL ServerSQL Server 登录帐户以及指定的密码是否与以前记录的密码匹配,对该连接进行身份验证。When a user connects with a specified login name and password from a non-trusted connection, SQL ServerSQL Server authenticates the connection by checking to see if a SQL ServerSQL Server login account has been set up and if the specified password matches the one previously recorded. 如果未设置 SQL ServerSQL Server 登录帐户,则身份验证失败,并且用户会收到一条错误消息。If SQL ServerSQL Server does not have a login account set, authentication fails, and the user receives an error message.

User nameUser name
使用 SQL Server 身份验证时,指定用户名。Specify a user name when you are using SQL Server Authentication.

密码Password
使用 SQL Server 身份验证时,提供密码。Provide the password when you are using SQL Server Authentication.

文件夹Folder
键入包路径,或通过单击“浏览” 导航到该包。Type the package path, or navigate to the package by clicking Browse.

设置“选择包”页上的选项Set Options on the Select Packages Page

包名称Package Name
列出包文件。Lists the package file.

“状态”Status
指示包是否已准备好转换为项目部署模型。Indicates whether a package is ready to convert to the project deployment model.

消息Message
显示与包关联的消息。Displays a message associated with the package.

密码Password
显示与包关联的密码。Displays a password associated with the package. 密码文本将被隐藏。The password text is hidden.

应用于所选内容Apply to selection
单击以将“密码” 文本框中的密码应用于一个或多个所选包。Click to apply the password in the Password text box, to the selected package or packages.

“刷新”Refresh
刷新包的列表。Refreshes the list of packages.

设置“选择目标”页上的选项Set Options on the Select Destination Page

在此页上,指定新的项目部署文件 (.ispac) 的名称和路径或者选择一个现有文件。On this page, specify the name and path for a new project deployment file (.ispac) or select an existing file.

备注

只有在从 Management StudioManagement Studio 运行该向导时,“选择目标” 页才可用。The Select Destination page is available only when you run the wizard from Management StudioManagement Studio.

输出路径Output path
键入部署文件的路径,或通过单击“浏览” 导航到该文件。Type the path for the deployment file or navigate to the file by clicking Browse.

项目名称Project name
键入项目名称。Type the project name.

保护级别Protection level
选择保护级别。Select the protection level. 有关详细信息,请参阅 Access Control for Sensitive Data in PackagesFor more information, see Access Control for Sensitive Data in Packages.

项目说明Project description
键入项目的可选说明。Type an optional description for the project.

设置“指定项目属性”页上的选项Set Options on the Specify Project Properties Page

备注

只有在从 Visual StudioVisual Studio运行该向导时,“指定项目属性” 页才可用。The Specify Project Properties page is available only when you run the wizard from Visual StudioVisual Studio.

项目名称Project name
列出项目名称。Lists the project name.

保护级别Protection level
为项目中所含的包选择保护级别。Select a protection level for the packages contained in the project. 有关保护级别的详细信息,请参阅 Access Control for Sensitive Data in PackagesFor more information about protection levels, see Access Control for Sensitive Data in Packages.

项目说明Project description
键入可选的项目说明。Type an optional project description.

设置“更新执行包任务”页上的选项Set Options on the Update Execute Package Task Page

更新包中所含的执行包任务,以使用基于项目的引用。Update Execute Package Tasks contain in the packages, to use a project-based reference. 有关详细信息,请参阅 Execute Package Task EditorFor more information, see Execute Package Task Editor.

父包Parent Package
列出使用执行包任务执行子包的包名称。Lists the name of the package that executes the child package using the Execute Package task.

任务名称Task name
列出执行包任务的名称。Lists the name of the Execute Package task.

原始引用Original reference
列出子包的当前路径。Lists the current path of the child package.

分配引用Assign reference
选择存储在项目中的子包。Select a child package stored in the project.

设置“选择配置”页上的选项Set Options on the Select Configurations Page

选择您要用参数替换的包配置。Select the package configurations that you want to replace with parameters.

“包”Package
列出包文件。Lists the package file.

类型Type
列出配置类型,如 XML 配置文件。Lists the type of configuration, such as an XML configuration file.

配置字符串Configuration String
列出配置文件的路径。Lists the path of the configuration file.

“状态”Status
显示配置的状态消息。Displays a status message for the configuration. 单击该消息可以查看整个消息文本。Click the message to view the entire message text.

添加配置Add Configurations
将在其他项目中包含的包配置添加到要用参数替换的可用配置的列表中。Add package configurations contained in other projects to the list of available configurations that you want to replace with parameters. 您可以选择存储在文件系统或 SQL Server 中的配置。You can select configurations stored in a file system or stored in SQL Server.

“刷新”Refresh
单击以刷新配置列表。Click to refresh the list of configurations.

在转换后删除所有包的配置Remove configurations from all packages after conversion
建议通过选择此选项从项目中删除所有配置。It is recommended that you remove all configurations from the project by selecting this option.

如果没有选择此选项,将只删除已选择用参数替换的配置。If you don't select this option, only the configurations that you selected to replace with parameters are removed.

设置“创建参数”页上的选项Set Options on the Create Parameters Page

选择每个配置属性的参数名称和作用域。Select the parameter name and scope for each configuration property.

“包”Package
列出包文件。Lists the package file.

参数名称Parameter Name
列出参数名称。Lists the parameter name.

范围Scope
选择参数的作用域(包或项目)。Select the scope of the parameter, either package or project.

设置“配置参数”页上的选项Set Options on the Configure Parameters Page

名称Name
列出参数名称。Lists the parameter name.

范围Scope
列出参数的作用域。Lists the scope of the parameter.

ReplTest1Value
列出参数值。Lists the parameter value.

单击值字段旁边的省略号按钮以配置参数属性。Click the ellipsis button next to the value field to configure the parameter properties.

“设置参数详细信息” 对话框中,可以编辑参数值。In the Set Parameter Details dialog box, you can edit the parameter value. 还可以指定在运行包时是否必须提供参数值。You can also specify whether the parameter value must be provided when you run the package.

您可以通过单击参数旁的“浏览”按钮,在 “配置” 对话框的 “参数” Management StudioManagement Studio页中修改值。You can modify value in the Parameters page of the Configure dialog box in Management StudioManagement Studio, by clicking the browse button next to the parameter. 将显示 “设置参数值” 对话框。The Set Parameter Value dialog box appears.

“设置参数详细信息” 对话框还列出参数值的数据类型和参数的来源。The Set Parameter Details dialog box also lists the data type of the parameter value and the origin of the parameter.

设置“检查”页上的选项Set the Options on the Review page

使用“检查”页可以确认为项目转换选择的选项 。Use the Review page to confirm the options that you've selected for the conversion of the project.

“上一步”Previous
单击以更改选项。Click to change an option.

转换Convert
单击以将项目转换为项目部署模型。Click to convert the project to the project deployment model.

设置执行转换的选项Set the Options on the Perform Conversion

“执行转换”页显示项目转换的状态。The Perform Conversion page shows status of the project conversion.

操作Action
列出特定的转换步骤。Lists a specific conversion step.

结果Result
列出每个转换步骤的状态。Lists the status of each conversion step. 单击状态消息可获取详细信息。Click the status message for more information.

直到在 Visual StudioVisual Studio中保存项目后,才保存项目转换。The project conversion is not saved until the project is saved in Visual StudioVisual Studio.

保存报告Save report
单击以在 .xml 文件中保存项目转换的摘要。Click to save a summary of the project conversion in an .xml file.