在 Azure 数据工厂中使用“执行 SSIS 包”活动运行 SSIS 包Run an SSIS package with the Execute SSIS Package activity in Azure Data Factory

本文介绍如何使用“执行 SSIS 包”活动在 Azure 数据工厂管道中运行 SQL Server Integration Services (SSIS) 包。This article describes how to run a SQL Server Integration Services (SSIS) package in an Azure Data Factory pipeline by using the Execute SSIS Package activity.

先决条件Prerequisites

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

如果还没有 Azure-SSIS Integration Runtime (IR),请按照以下文章中的分步说明创建 IR:教程:预配 Azure-SSIS IRCreate an Azure-SSIS integration runtime (IR) if you don't have one already by following the step-by-step instructions in the Tutorial: Provisioning Azure-SSIS IR.

在 Azure 门户中运行包Run a package in the Azure portal

在本部分中,我们将使用数据工厂用户界面 (UI) 或应用来创建一个数据工厂管道,其中包含可运行 SSIS 包的“执行 SSIS 包”活动。In this section, you use the Data Factory user interface (UI) or app to create a Data Factory pipeline with an Execute SSIS Package activity that runs your SSIS package.

使用“执行 SSIS 包”活动创建管道Create a pipeline with an Execute SSIS Package activity

此步骤使用数据工厂 UI 或应用创建管道。In this step, you use the Data Factory UI or app to create a pipeline. 将“执行 SSIS 包”活动添加到管道,并将该活动配置为运行 SSIS 包。You add an Execute SSIS Package activity to the pipeline and configure it to run your SSIS package.

  1. 在 Azure 门户中的数据工厂概述或主页上,选择“创作和监视”磁贴,在单独的选项卡中启动数据工厂 UI 或应用。 On your Data Factory overview or home page in the Azure portal, select the Author & Monitor tile to start the Data Factory UI or app in a separate tab.

    数据工厂主页

    在“开始使用”页中,选择“创建管道”。 On the Let's get started page, select Create pipeline.

    “入门”页

  2. 在“活动” 工具箱中,展开“常规” 。In the Activities toolbox, expand General. 然后将“执行 SSIS 包”活动拖到管道设计图面上。 Then drag an Execute SSIS Package activity to the pipeline designer surface.

    将“执行 SSIS 包”活动拖到设计面

  3. 在“执行 SSIS 包”活动的“常规”选项卡上,提供活动的名称和说明 。On the General tab for the Execute SSIS Package activity, provide a name and description for the activity. 设置可选的“超时”和“重试”值。 Set optional Timeout and Retry values.

    在“常规”选项卡上设置属性

  4. 在“执行 SSIS 包”活动的“设置”选项卡上,选择要在其中运行包的 Azure-SSIS IR。 On the Settings tab for the Execute SSIS Package activity, select an Azure-SSIS IR where you want to run your package. 如果包使用 Windows 身份验证访问数据存储(例如本地的 SQL Server 或文件共享,或 Azure 文件存储),请选中“Windows 身份验证”复选框。 If your package uses Windows authentication to access data stores (for example, SQL servers or file shares on-premises or Azure Files), select the Windows authentication check box. 在“域”、“用户名”和“密码”框中输入包执行凭据的值。 Enter the values for your package execution credentials in the Domain, Username, and Password boxes.

    或者,可以将 Azure Key Vault 中存储的机密用作其值。Alternatively, you can use secrets stored in your Azure key vault as their values. 为此,请选中相关凭据旁边的“AZURE KEY VAULT”复选框。 To do so, select the AZURE KEY VAULT check box next to the relevant credential. 选择或编辑现有的 Key Vault 链接服务,或创建新的链接服务。Select or edit your existing key vault linked service or create a new one. 然后为凭据值选择机密名称或版本。Then select the secret name or version for your credential value.

    创建或编辑 Key Vault 链接服务时,可以选择或编辑现有的 Key Vault,或创建新的 Key Vault。When you create or edit your key vault linked service, you can select or edit your existing key vault or create a new one. 请务必授予数据工厂托管标识对 Key Vault 的访问权限(如果尚未这样做)。Make sure to grant Data Factory managed identity access to your key vault if you haven't done so already. 此外,还可以采用以下格式直接输入机密:<Key vault linked service name>/<secret name>/<secret version>You can also enter your secrets directly in the following format: <Key vault linked service name>/<secret name>/<secret version>. 如果包需要 32 位运行时才能运行,请选中“32 位运行时”复选框 。If your package needs 32-bit runtime to run, select the 32-Bit runtime check box.

    对于“包位置”,请选择“SSISDB”、“文件系统(包)”、“文件系统(项目)”或“嵌入包”。 For Package location, select SSISDB, File System (Package), File System (Project), or Embedded package. 如果选择“SSISDB”作为包位置(如果为 Azure-SSIS IR 预配了 Azure SQL 数据库服务器或托管实例托管的 SSIS 目录 (SSISDB),则会自动选择该选项),请指定要运行的、已部署到 SSISDB 中的包。 If you select SSISDB as your package location, which is automatically selected if your Azure-SSIS IR was provisioned with the SSIS catalog (SSISDB) hosted by an Azure SQL Database server or managed instance, specify your package to run that was deployed into SSISDB.

    如果 Azure-SSIS IR 正在运行且清除了“手动输入内容”复选框,可以从 SSISDB 浏览并选择现有的文件夹、项目、包或环境 。If your Azure-SSIS IR is running and the Manual entries check box is cleared, browse and select your existing folders, projects, packages, or environments from SSISDB. 选择“刷新”以从 SSISDB 获取新添加的文件夹、项目、包或环境,以便可以浏览和选择这些内容 。Select Refresh to fetch your newly added folders, projects, packages, or environments from SSISDB so that they're available for browsing and selection. 若要浏览或选择包执行的环境,必须事先配置项目,以便从 SSISDB 下的相同文件夹中添加这些环境作为引用。To browse or select the environments for your package executions, you must configure your projects beforehand to add those environments as references from the same folders under SSISDB. 有关详细信息,请参阅创建和映射 SSIS 环境For more information, see Create and map SSIS environments.

    对于“日志记录级别”,请为包执行选择预定义的日志记录范围 。For Logging level, select a predefined scope of logging for your package execution. 如果要改为输入自定义日志记录名称,请选中“自定义”复选框 。Select the Customized check box if you want to enter your customized logging name instead.

    在“设置”选项卡上设置属性 - 自动

    如果 Azure-SSIS IR 未在运行或“手动输入内容” 复选框处于选中状态,请采用以下格式直接在 SSISDB 中输入你的包和环境路径:<folder name>/<project name>/<package name>.dtsx<folder name>/<environment name>If your Azure-SSIS IR isn't running or the Manual entries check box is selected, enter your package and environment paths from SSISDB directly in the following formats: <folder name>/<project name>/<package name>.dtsx and <folder name>/<environment name>.

    在“设置”选项卡上设置属性 - 手动

    如果选择“文件系统(包)”作为包位置(如果未为 Azure-SSIS IR 预配 SSISDB,则会自动选择该选项),请通过在“包路径”框中提供包文件 (.dtsx) 的通用命名约定 (UNC) 路径来指定要运行的包。 If you select File System (Package) as your package location, which is automatically selected if your Azure-SSIS IR was provisioned without SSISDB, specify your package to run by providing a Universal Naming Convention (UNC) path to your package file (.dtsx) in the Package path box. 例如,如果将包存储在 Azure 文件存储中,则其包路径为 \\<storage account name>.file.core.chinacloudapi.cn\<file share name>\<package name>.dtsxFor example, if you store your package in Azure Files, its package path is \\<storage account name>.file.core.chinacloudapi.cn\<file share name>\<package name>.dtsx.

    如果在单独的文件中配置包,则还需要在“配置路径”框中提供配置文件 (.dtsConfig) 的 UNC 路径。 If you configure your package in a separate file, you also need to provide a UNC path to your configuration file (.dtsConfig) in the Configuration path box. 例如,如果将配置存储在 Azure 文件存储中,则其配置路径为 \\<storage account name>.file.core.chinacloudapi.cn\<file share name>\<configuration name>.dtsConfigFor example, if you store your configuration in Azure Files, its configuration path is \\<storage account name>.file.core.chinacloudapi.cn\<file share name>\<configuration name>.dtsConfig.

    在“设置”选项卡上设置属性 - 手动

    如果选择“文件系统(项目)”作为包位置,请通过在“项目路径”框中提供项目文件 (.ispac) 的 UNC 路径,并在“包名称”框中提供项目中某个包文件 (.dtsx) 的 UNC 路径,来指定要运行的包。 If you select File System (Project) as your package location, specify your package to run by providing a UNC path to your project file (.ispac) in the Project path box and a package file (.dtsx) from your project in the Package name box. 例如,如果将项目存储在 Azure 文件存储中,则其项目路径为 \\<storage account name>.file.core.chinacloudapi.cn\<file share name>\<project name>.ispacFor example, if you store your project in Azure Files, its project path is \\<storage account name>.file.core.chinacloudapi.cn\<file share name>\<project name>.ispac.

    在“设置”选项卡上设置属性 - 手动

    接下来,指定用于访问项目、包或配置文件的凭据。Next, specify the credentials to access your project, package, or configuration files. 如果先前已输入包执行凭据的值(参阅上文),则可以通过选中“与包执行凭据相同”复选框来重复使用这些值。 If you previously entered the values for your package execution credentials (see previous), you can reuse them by selecting the Same as package execution credentials check box. 否则,请在“域”、“用户名”和“密码”框中输入包访问凭据的值。 Otherwise, enter the values for your package access credentials in the Domain, Username, and Password boxes. 例如,如果将项目、包或配置存储在 Azure 文件存储中,则域为 Azure,用户名为 <storage account name>,密码为 <storage account key>For example, if you store your project, package, or configuration in Azure Files, the domain is Azure, the username is <storage account name>, and the password is <storage account key>.

    或者,可将 Key Vault 中存储的机密用作其值(参阅上文)。Alternatively, you can use secrets stored in your key vault as their values (see previous). 这些凭据用于访问“执行包任务”中的包和子包,全部来自其自身的路径或相同的项目以及配置(包括包中指定的配置)。These credentials are used to access your package and child packages in Execute Package Task, all from their own path or the same project, as well as configurations, which include those specified in your packages.

    如果选择“嵌入包”作为包位置,请通过拖放方式来运行包,或将其从文件夹“上传”到提供的框中。 If you select Embedded package as your package location, drag and drop your package to run or Upload it from a file folder into the box provided. 包会自动压缩并嵌入到活动有效负载中。Your package will be automatically compressed and embedded in the activity payload. 嵌入后,可以“下载”包供以后编辑。 Once embedded, you can Download your package later for editing. 你还可以通过将嵌入包分配给可在多个活动中使用的管道参数来参数化它,从而优化管道有效负载的大小。You can also Parameterize your embedded package by assigning it to a pipeline parameter that can be used in multiple activities, hence optimizing the size of your pipeline payload. 如果嵌入包不是全部加密的,并且我们检测到其中使用了“执行包任务”,则系统会自动选择“执行包任务”复选框,并自动添加相关子包及其文件系统引用,以便你也嵌入它们。 If your embedded package is not all encrypted and we detect the use of Execute Package Task in it, the Execute Package Task check box will be automatically selected and the relevant child packages with their file system references will be automatically added for you to also embed them. 如果无法检测到“执行包任务”的使用,则必须手动选中“执行包任务”复选框,并添加相关子包,其文件系统会逐个引用它们,这样你就也可以嵌入它们。 If we can't detect the use of Execute Package Task, you'll have to manually select the Execute Package Task check box and add the relevant child packages with their file system references one by one for you to also embed them. 如果子包使用 SQL Server 引用,请确保 Azure-SSIS IR 可以访问 SQL Server。If the child packages use SQL Server references, please ensure that the SQL Server is accessible by your Azure-SSIS IR. 当前不支持为子包使用项目引用。The use of project references for child packages is currently unsupported.

    在“设置”选项卡上设置属性 - 手动

    如果在通过 SQL Server Data Tools 创建包时使用了 EncryptAllWithPasswordEncryptSensitiveWithPassword 保护级别,请在“加密密码”框中输入密码的值。 If you used the EncryptAllWithPassword or EncryptSensitiveWithPassword protection level when you created your package via SQL Server Data Tools, enter the value for your password in the Encryption password box. 或者,可将 Key Vault 中存储的机密用作其值(参阅上文)。Alternatively, you can use a secret stored in your key vault as its value (see previous). 如果使用了 EncryptSensitiveWithUserKey 保护级别,请在配置文件中或在“SSIS 参数”、“连接管理器”或“属性替代”选项卡上重新输入敏感值(参阅下文)。 If you used the EncryptSensitiveWithUserKey protection level, reenter your sensitive values in configuration files or on the SSIS Parameters, Connection Managers, or Property Overrides tabs (see later).

    不支持使用 EncryptAllWithUserKey 保护级别。If you used the EncryptAllWithUserKey protection level, it's unsupported. 需要通过 SQL Server Data Tools 或 dtutil 命令行实用工具将包重新配置为使用另一保护级别。You need to reconfigure your package to use another protection level via SQL Server Data Tools or the dtutil command-line utility.

    对于“日志记录级别”,请为包执行选择预定义的日志记录范围 。For Logging level, select a predefined scope of logging for your package execution. 如果要改为输入自定义日志记录名称,请选中“自定义”复选框 。Select the Customized check box if you want to enter your customized logging name instead. 若要记录包执行但不使用可在包中指定的标准日志提供程序,请通过在“日志记录路径”框中提供其 UNC 路径来指定日志文件夹。 If you want to log your package executions beyond using the standard log providers that can be specified in your package, specify your log folder by providing its UNC path in the Logging path box. 例如,如果将日志存储在 Azure 文件存储中,则日志记录路径为 \\<storage account name>.file.core.chinacloudapi.cn\<file share name>\<log folder name>For example, if you store your logs in Azure Files, your logging path is \\<storage account name>.file.core.chinacloudapi.cn\<file share name>\<log folder name>. 将在此路径中为每个包运行创建一个与“执行 SSIS 包”活动运行 ID 同名的子文件夹,其中的日志文件每隔 5 分钟生成一次。A subfolder is created in this path for each individual package run and named after the Execute SSIS Package activity run ID, in which log files are generated every five minutes.

    最后,请指定用于访问日志文件夹的凭据。Finally, specify the credentials to access your log folder. 如果先前已输入包访问凭据的值(参阅上文),则可以通过选中“与包访问凭据相同”复选框来重复使用这些值。 If you previously entered the values for your package access credentials (see previous), you can reuse them by selecting the Same as package access credentials check box. 否则,请在“域”、“用户名”和“密码”框中输入日志记录访问凭据的值。 Otherwise, enter the values for your logging access credentials in the Domain, Username, and Password boxes. 例如,如果将日志存储在 Azure 文件存储中,则域为 Azure,用户名为 <storage account name>,密码为 <storage account key>For example, if you store your logs in Azure Files, the domain is Azure, the username is <storage account name>, and the password is <storage account key>.

    或者,可将 Key Vault 中存储的机密用作其值(参阅上文)。Alternatively, you can use secrets stored in your key vault as their values (see previous). 这些凭据用于存储日志。These credentials are used to store your logs.

    对于上述所有 UNC 路径,完全限定的文件名必须短于 260 个字符。For all UNC paths previously mentioned, the fully qualified file name must be fewer than 260 characters. 目录名称必须短于 248 个字符。The directory name must be fewer than 248 characters.

  5. 在“执行 SSIS 包”活动的“SSIS 参数”选项卡上,如果 Azure-SSIS IR 正在运行、已选择“SSISDB”作为包位置,且已清除“设置”选项卡上的“手动输入内容”复选框,则会显示 SSISDB 中选定项目或包中现有的 SSIS 参数,以便为它们赋值 。On the SSIS Parameters tab for the Execute SSIS Package activity, if your Azure-SSIS IR is running, SSISDB is selected as your package location, and the Manual entries check box on the Settings tab is cleared, the existing SSIS parameters in your selected project or package from SSISDB are displayed for you to assign values to them. 否则,可以逐个输入以便手动为它们赋值。Otherwise, you can enter them one by one to assign values to them manually. 为了使包成功执行,请确保它们存在并已正确输入。Make sure that they exist and are correctly entered for your package execution to succeed.

    如果通过 SQL Server Data Tools 创建包时使用了 EncryptSensitiveWithUserKey 保护级别,且已选择“文件系统(包)”或“文件系统(项目)”作为包位置,则还需要重新输入敏感参数,以便在配置文件或此选项卡上为它们赋值。 If you used the EncryptSensitiveWithUserKey protection level when you created your package via SQL Server Data Tools and File System (Package) or File System (Project) is selected as your package location, you also need to reenter your sensitive parameters to assign values to them in configuration files or on this tab.

    为参数赋值时,可以使用表达式、函数、数据工厂系统变量和数据工厂管道参数或变量添加动态内容。When you assign values to your parameters, you can add dynamic content by using expressions, functions, Data Factory system variables, and Data Factory pipeline parameters or variables. 或者,可将 Key Vault 中存储的机密用作其值(参阅上文)。Alternatively, you can use secrets stored in your key vault as their values (see previous).

    在“SSIS 参数”选项卡上设置属性

  6. 在用于“执行 SSIS 包”活动的“连接管理器”选项卡上,如果 Azure-SSIS IR 正在运行、已选择“SSISDB”作为包位置,且已清除“设置”选项卡上的“手动输入内容”复选框,则 SSISDB 中的选定项目或包中的现有连接管理器将会显示,供你为其属性赋值 。On the Connection Managers tab for the Execute SSIS Package activity, if your Azure-SSIS IR is running, SSISDB is selected as your package location, and the Manual entries check box on the Settings tab is cleared, the existing connection managers in your selected project or package from SSISDB are displayed for you to assign values to their properties. 否则,可以逐个输入以便手动为其属性赋值。Otherwise, you can enter them one by one to assign values to their properties manually. 为了使包成功执行,请确保它们存在并已正确输入。Make sure that they exist and are correctly entered for your package execution to succeed.

    如果通过 SQL Server Data Tools 创建包时使用了 EncryptSensitiveWithUserKey 保护级别,且已选择“文件系统(包)”或“文件系统(项目)”作为包位置,则还需要重新输入敏感的连接管理器属性,以便在配置文件或此选项卡上为它们赋值。 If you used the EncryptSensitiveWithUserKey protection level when you created your package via SQL Server Data Tools and File System (Package) or File System (Project) is selected as your package location, you also need to reenter your sensitive connection manager properties to assign values to them in configuration files or on this tab.

    为连接管理器属性赋值时,可以使用表达式、函数、数据工厂系统变量和数据工厂管道参数或变量添加动态内容。When you assign values to your connection manager properties, you can add dynamic content by using expressions, functions, Data Factory system variables, and Data Factory pipeline parameters or variables. 或者,可将 Key Vault 中存储的机密用作其值(参阅上文)。Alternatively, you can use secrets stored in your key vault as their values (see previous).

    在“连接管理器”选项卡上设置属性

  7. 在“执行 SSIS 包”活动的“属性替代”选项卡上,逐个输入选定包的现有属性的路径,以便手动为其赋值。 On the Property Overrides tab for the Execute SSIS Package activity, enter the paths of existing properties in your selected package one by one to assign values to them manually. 为了使包成功执行,请确保它们存在并已正确输入。Make sure that they exist and are correctly entered for your package execution to succeed. 例如,若要替代用户变量的值,请按以下格式输入其路径:\Package.Variables[User::<variable name>].ValueFor example, to override the value of your user variable, enter its path in the following format: \Package.Variables[User::<variable name>].Value.

    如果通过 SQL Server Data Tools 创建包时使用了 EncryptSensitiveWithUserKey 保护级别,且已选择“文件系统(包)”或“文件系统(项目)”作为包位置,则还需要重新输入敏感属性,以便在配置文件或此选项卡上为它们赋值。 If you used the EncryptSensitiveWithUserKey protection level when you created your package via SQL Server Data Tools and File System (Package) or File System (Project) is selected as your package location, you also need to reenter your sensitive properties to assign values to them in configuration files or on this tab.

    为属性赋值时,可以使用表达式、函数、数据工厂系统变量和数据工厂管道参数或变量添加动态内容。When you assign values to your properties, you can add dynamic content by using expressions, functions, Data Factory system variables, and Data Factory pipeline parameters or variables.

    在“属性替代”选项卡上设置属性

    可以使用“连接管理器”或“属性替代”选项卡替代配置文件中和“SSIS 参数”选项卡上的赋值。 The values assigned in configuration files and on the SSIS Parameters tab can be overridden by using the Connection Managers or Property Overrides tabs. 还可以使用“属性替代”选项卡替代“连接管理器”选项卡上的赋值。 The values assigned on the Connection Managers tab can also be overridden by using the Property Overrides tab.

  8. 若要验证管道配置,请在工具栏中选择“验证” 。To validate the pipeline configuration, select Validate on the toolbar. 若要关闭“管道验证报告”, 请选择 >>To close the Pipeline Validation Report, select >>.

  9. 若要将管道发布到数据工厂,请选择“全部发布” 。To publish the pipeline to Data Factory, select Publish All.

运行管道Run the pipeline

在此步骤中,将触发管道运行。In this step, you trigger a pipeline run.

  1. 若要触发某个管道运行,请在工具栏中选择“触发器” ,然后选择“立即触发” 。To trigger a pipeline run, select Trigger on the toolbar, and select Trigger now.

    立即触发

  2. 在“管道运行”窗口中选择“完成”。 In the Pipeline Run window, select Finish.

监视管道Monitor the pipeline

  1. 在左侧切换到“监视”选项卡。 Switch to the Monitor tab on the left. 随即显示管道运行及其状态和其他信息(例如“运行开始”时间)。 You see the pipeline run and its status along with other information, such as the Run Start time. 若要刷新视图,请选择“刷新”。 To refresh the view, select Refresh.

    管道运行

  2. 在“操作”列中选择“查看活动运行”链接。 Select the View Activity Runs link in the Actions column. 此时只显示一个活动运行,因为管道只有一个活动。You see only one activity run because the pipeline has only one activity. 该活动为“执行 SSIS 包”活动。It's the Execute SSIS Package activity.

    活动运行

  3. 在 SQL Server 中针对 SSISDB 数据库运行以下查询,验证是否执行了该包。Run the following query against the SSISDB database in your SQL server to verify that the package executed.

    select * from catalog.executions
    

    验证包执行

  4. 还可以从管道活动运行的输出中获取 SSISDB 执行 ID,并使用此 ID 在 SQL Server Management Studio 中查看更全面的执行日志和错误消息。You can also get the SSISDB execution ID from the output of the pipeline activity run and use the ID to check more comprehensive execution logs and error messages in SQL Server Management Studio.

    获取执行 ID。

使用触发器计划管道Schedule the pipeline with a trigger

还可以为管道创建一个计划触发器,以便按计划(例如每小时或每天)运行管道。You can also create a scheduled trigger for your pipeline so that the pipeline runs on a schedule, such as hourly or daily. 有关示例,请参阅创建数据工厂 - 数据工厂 UIFor an example, see Create a data factory - Data Factory UI.

使用 PowerShell 运行包Run a package with PowerShell

在此部分中,将使用 Azure PowerShell 创建一个数据工厂管道,管道中包含可运行 SSIS 包的“执行 SSIS 包”活动。In this section, you use Azure PowerShell to create a Data Factory pipeline with an Execute SSIS Package activity that runs your SSIS package.

按照如何安装和配置 Azure PowerShell 中的分步说明安装最新的 Azure PowerShell 模块。Install the latest Azure PowerShell modules by following the step-by-step instructions in How to install and configure Azure PowerShell.

创建包含 Azure-SSIS IR 的数据工厂Create a data factory with Azure-SSIS IR

可以使用已预配 Azure-SSIS IR 的现有数据工厂,或者创建包含 Azure-SSIS IR 的新数据工厂。You can either use an existing data factory that already has Azure-SSIS IR provisioned or create a new data factory with Azure-SSIS IR. 按照教程:通过 PowerShell 将 SSIS 包部署到 Azure 中的分步说明,创建包含 Azure-SSIS IR 的新 ADF。Follow the step-by-step instructions in the Tutorial: Deploy SSIS packages to Azure via PowerShell.

使用“执行 SSIS 包”活动创建管道Create a pipeline with an Execute SSIS Package activity

在此步骤中创建包含“执行 SSIS 包”活动的管道。In this step, you create a pipeline with an Execute SSIS Package activity. 该活动运行 SSIS 包。The activity runs your SSIS package.

  1. C:\ADF\RunSSISPackage 文件夹中创建名为 RunSSISPackagePipeline.json 的 JSON 文件,并在其中包含类似于以下示例的内容。Create a JSON file named RunSSISPackagePipeline.json in the C:\ADF\RunSSISPackage folder with content similar to the following example.

    Important

    在保存该文件之前,请替换对象名称、说明、路径、属性或参数值、密码及其他变量值。Replace object names, descriptions, and paths, property or parameter values, passwords, and other variable values before you save the file.

    {
        "name": "RunSSISPackagePipeline",
        "properties": {
            "activities": [{
                "name": "MySSISActivity",
                "description": "My SSIS package/activity description",
                "type": "ExecuteSSISPackage",
                "typeProperties": {
                    "connectVia": {
                        "referenceName": "MyAzureSSISIR",
                        "type": "IntegrationRuntimeReference"
                    },
                    "executionCredential": {
                        "domain": "MyExecutionDomain",
                        "username": "MyExecutionUsername",
                        "password": {
                            "type": "SecureString",
                            "value": "MyExecutionPassword"
                        }
                    },
                    "runtime": "x64",
                    "loggingLevel": "Basic",
                    "packageLocation": {
                        "packagePath": "MyFolder/MyProject/MyPackage.dtsx",
                        "type": "SSISDB"
                    },
                    "environmentPath": "MyFolder/MyEnvironment",
                    "projectParameters": {
                        "project_param_1": {
                            "value": "123"
                        },
                        "project_param_2": {
                            "value": {
                                "value": "@pipeline().parameters.MyProjectParameter",
                                "type": "Expression"
                            }
                        }
                    },
                    "packageParameters": {
                        "package_param_1": {
                            "value": "345"
                        },
                        "package_param_2": {
                            "value": {
                                "type": "AzureKeyVaultSecret",
                                "store": {
                                    "referenceName": "myAKV",
                                    "type": "LinkedServiceReference"
                                },
                                "secretName": "MyPackageParameter"
                            }
                        }
                    },
                    "projectConnectionManagers": {
                        "MyAdonetCM": {
                            "username": {
                                "value": "MyConnectionUsername"
                            },
                            "password": {
                                "value": {
                                    "type": "SecureString",
                                    "value": "MyConnectionPassword"
                                }
                            }
                        }
                    },
                    "packageConnectionManagers": {
                        "MyOledbCM": {
                            "username": {
                                "value": {
                                    "value": "@pipeline().parameters.MyConnectionUsername",
                                    "type": "Expression"
                                }
                            },
                            "password": {
                                "value": {
                                    "type": "AzureKeyVaultSecret",
                                    "store": {
                                        "referenceName": "myAKV",
                                        "type": "LinkedServiceReference"
                                    },
                                    "secretName": "MyConnectionPassword",
                                    "secretVersion": "MyConnectionPasswordVersion"
                                }
                            }
                        }
                    },
                    "propertyOverrides": {
                        "\\Package.MaxConcurrentExecutables": {
                            "value": 8,
                            "isSensitive": false
                        }
                    }
                },
                "policy": {
                    "timeout": "0.01:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30
                }
            }]
        }
    }
    

    若要执行存储在文件系统、文件共享或 Azure 文件存储中的包,请输入包和日志位置属性的值,如下所示:To execute packages stored in file systems, file shares, or Azure Files, enter the values for your package and log location properties as follows:

    {
        {
            {
                {
                    "packageLocation": {
                        "packagePath": "//MyStorageAccount.file.core.chinacloudapi.cn/MyFileShare/MyPackage.dtsx",
                        "type": "File",
                        "typeProperties": {
                            "packagePassword": {
                                "type": "SecureString",
                                "value": "MyEncryptionPassword"
                            },
                            "accessCredential": {
                                "domain": "Azure",
                                "username": "MyStorageAccount",
                                "password": {
                                    "type": "SecureString",
                                    "value": "MyAccountKey"
                                }
                            }
                        }
                    },
                    "logLocation": {
                        "logPath": "//MyStorageAccount.file.core.chinacloudapi.cn/MyFileShare/MyLogFolder",
                        "type": "File",
                        "typeProperties": {
                            "accessCredential": {
                                "domain": "Azure",
                                "username": "MyStorageAccount",
                                "password": {
                                    "type": "AzureKeyVaultSecret",
                                    "store": {
                                        "referenceName": "myAKV",
                                        "type": "LinkedServiceReference"
                            },
                                    "secretName": "MyAccountKey"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    

    若要执行存储在文件系统、文件共享或 Azure 文件存储中项目中的包,请输入包位置属性的值,如下所示:To execute packages within projects stored in file systems, file shares, or Azure Files, enter the values for your package location property as follows:

    {
        {
            {
                {
                    "packageLocation": {
                        "packagePath": "//MyStorageAccount.file.core.chinacloudapi.cn/MyFileShare/MyProject.ispac:MyPackage.dtsx",
                        "type": "File",
                        "typeProperties": {
                            "packagePassword": {
                                "type": "SecureString",
                                "value": "MyEncryptionPassword"
                            },
                            "accessCredential": {
                                "domain": "Azure",
                                "userName": "MyStorageAccount",
                                "password": {
                                    "type": "SecureString",
                                    "value": "MyAccountKey"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    

    若要执行嵌入包,请输入包位置属性的值,如下所示:To execute embedded packages, enter the values for your package location property as follows:

    {
        {
            {
                {
                    "packageLocation": {
                        "type": "InlinePackage",
                        "typeProperties": {
                            "packagePassword": {
                                "type": "SecureString",
                                "value": "MyEncryptionPassword"
                            },
                            "packageName": "MyPackage.dtsx",
                            "packageContent":"My compressed/uncompressed package content",
                            "packageLastModifiedDate": "YYYY-MM-DDTHH:MM:SSZ UTC-/+HH:MM"
                        }
                    }
                }
            }
        }
    }
    
  2. 在 Azure PowerShell 中,切换到 C:\ADF\RunSSISPackage 文件夹。In Azure PowerShell, switch to the C:\ADF\RunSSISPackage folder.

  3. 若要创建管道 RunSSISPackagePipeline,请运行 Set-AzDataFactoryV2Pipeline cmdlet。To create the pipeline RunSSISPackagePipeline, run the Set-AzDataFactoryV2Pipeline cmdlet.

    $DFPipeLine = Set-AzDataFactoryV2Pipeline -DataFactoryName $DataFactory.DataFactoryName `
                                                   -ResourceGroupName $ResGrp.ResourceGroupName `
                                                   -Name "RunSSISPackagePipeline"
                                                   -DefinitionFile ".\RunSSISPackagePipeline.json"
    

    下面是示例输出:Here's the sample output:

    PipelineName      : Adfv2QuickStartPipeline
    ResourceGroupName : <resourceGroupName>
    DataFactoryName   : <dataFactoryName>
    Activities        : {CopyFromBlobToBlob}
    Parameters        : {[inputPath, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification], [outputPath, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification]}
    

运行管道Run the pipeline

使用 Invoke-AzDataFactoryV2Pipeline cmdlet 运行该管道。Use the Invoke-AzDataFactoryV2Pipeline cmdlet to run the pipeline. 此 cmdlet 返回管道运行 ID,用于将来的监视。The cmdlet returns the pipeline run ID for future monitoring.

$RunId = Invoke-AzDataFactoryV2Pipeline -DataFactoryName $DataFactory.DataFactoryName `
                                             -ResourceGroupName $ResGrp.ResourceGroupName `
                                             -PipelineName $DFPipeLine.Name

监视管道Monitor the pipeline

运行以下 PowerShell 脚本,持续检查管道运行状态,直到完成数据复制为止。Run the following PowerShell script to continuously check the pipeline run status until it finishes copying the data. 在 PowerShell 窗口中复制或粘贴以下脚本,然后按 Enter。Copy or paste the following script in the PowerShell window, and select Enter.

while ($True) {
    $Run = Get-AzDataFactoryV2PipelineRun -ResourceGroupName $ResGrp.ResourceGroupName `
                                               -DataFactoryName $DataFactory.DataFactoryName `
                                               -PipelineRunId $RunId

    if ($Run) {
        if ($run.Status -ne 'InProgress') {
            Write-Output ("Pipeline run finished. The status is: " +  $Run.Status)
            $Run
            break
        }
        Write-Output  "Pipeline is running...status: InProgress"
    }

    Start-Sleep -Seconds 10
}   

还可使用 Azure 门户监视管道。You can also monitor the pipeline by using the Azure portal. 有关分步说明,请参阅监视管道For step-by-step instructions, see Monitor the pipeline.

使用触发器计划管道Schedule the pipeline with a trigger

在上一步骤中,已按需运行了管道。In the previous step, you ran the pipeline on demand. 还可创建一个计划触发器,按计划(例如每小时或每天)运行管道。You can also create a schedule trigger to run the pipeline on a schedule, such as hourly or daily.

  1. C:\ADF\RunSSISPackage 文件夹中创建名为 MyTrigger.json 的 JSON 文件,并在其中包含以下内容:Create a JSON file named MyTrigger.json in the C:\ADF\RunSSISPackage folder with the following content:

    {
        "properties": {
            "name": "MyTrigger",
            "type": "ScheduleTrigger",
            "typeProperties": {
                "recurrence": {
                    "frequency": "Hour",
                    "interval": 1,
                    "startTime": "2017-12-07T00:00:00-08:00",
                    "endTime": "2017-12-08T00:00:00-08:00"
                }
            },
            "pipelines": [{
                "pipelineReference": {
                    "type": "PipelineReference",
                    "referenceName": "RunSSISPackagePipeline"
                },
                "parameters": {}
            }]
        }
    }    
    
  2. 在 Azure PowerShell 中,切换到 C:\ADF\RunSSISPackage 文件夹。In Azure PowerShell, switch to the C:\ADF\RunSSISPackage folder.

  3. 运行 Set-AzDataFactoryV2Trigger cmdlet,以创建触发器。Run the Set-AzDataFactoryV2Trigger cmdlet, which creates the trigger.

    Set-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName `
                                    -DataFactoryName $DataFactory.DataFactoryName `
                                    -Name "MyTrigger" -DefinitionFile ".\MyTrigger.json"
    
  4. 默认情况下,触发器处于停止状态。By default, the trigger is in stopped state. 运行 Start-AzDataFactoryV2Trigger cmdlet 以启动该触发器。Start the trigger by running the Start-AzDataFactoryV2Trigger cmdlet.

    Start-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName `
                                      -DataFactoryName $DataFactory.DataFactoryName `
                                      -Name "MyTrigger" 
    
  5. 通过运行 Get-AzDataFactoryV2Trigger cmdlet 确认该触发器已启动。Confirm that the trigger is started by running the Get-AzDataFactoryV2Trigger cmdlet.

    Get-AzDataFactoryV2Trigger -ResourceGroupName $ResourceGroupName `
                                    -DataFactoryName $DataFactoryName `
                                    -Name "MyTrigger"     
    
  6. 在下一小时后运行以下命令。Run the following command after the next hour. 例如,如果当前时间为下午 3:25 (UTC),则在下午 4:00 (UTC) 运行该命令。For example, if the current time is 3:25 PM UTC, run the command at 4 PM UTC.

    Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName `
                                       -DataFactoryName $DataFactoryName `
                                       -TriggerName "MyTrigger" `
                                       -TriggerRunStartedAfter "2017-12-06" `
                                       -TriggerRunStartedBefore "2017-12-09"
    

    在 SQL Server 中针对 SSISDB 数据库运行以下查询,验证是否执行了该包。Run the following query against the SSISDB database in your SQL server to verify that the package executed.

    select * from catalog.executions