使用支持 Azure 的 dtexec 实用工具运行 SQL Server Integration Services 包Run SQL Server Integration Services packages with the Azure-enabled dtexec utility

适用于:是 Azure 数据工厂否 Azure Synapse Analytics(预览版)APPLIES TO: yesAzure Data Factory noAzure Synapse Analytics (Preview)

本文介绍支持 Azure 的 dtexec (AzureDTExec) 命令提示符实用工具。This article describes the Azure-enabled dtexec (AzureDTExec) command prompt utility. 此实用工具用于在 Azure 数据工厂中的 Azure-SSIS Integration Runtime (IR) 上运行 SQL Server Integration Services (SSIS) 包。It's used to run SQL Server Integration Services (SSIS) packages on the Azure-SSIS Integration Runtime (IR) in Azure Data Factory.

SQL Server 随附了传统的 dtexec 实用工具。The traditional dtexec utility comes with SQL Server. 有关详细信息,请参阅 dtexec 实用工具For more information, see dtexec utility. 此实用工具通常由 ActiveBatch 和 Control-M 等第三方业务流程协调程序或计划程序调用,以在本地运行 SSIS 包。It's often invoked by third-party orchestrators or schedulers, such as ActiveBatch and Control-M, to run SSIS packages on-premises.

SQL Server Management Studio (SSMS) 工具随附了新式 AzureDTExec 实用工具。The modern AzureDTExec utility comes with a SQL Server Management Studio (SSMS) tool. 它也可以由第三方业务流程协调程序或计划程序调用,以在 Azure 中运行 SSIS 包。It can also be invoked by third-party orchestrators or schedulers to run SSIS packages in Azure. 此实用程序有助于将 SSIS 包直接转移或迁移到云中。It facilitates the lifting and shifting or migration of your SSIS packages to the cloud. 迁移后,如果你想要在日常运营中保持使用第三方业务流程协调程序或计划程序,这些程序现在可以调用 AzureDTExec 而不是 dtexec。After migration, if you want to keep using third-party orchestrators or schedulers in your day-to-day operations, they can now invoke AzureDTExec instead of dtexec.

AzureDTExec 将包作为数据工厂管道中的“执行 SSIS 包”活动运行。AzureDTExec runs your packages as Execute SSIS Package activities in Data Factory pipelines. 有关详细信息,请参阅将 SSIS 包作为 Azure 数据工厂活动运行For more information, see Run SSIS packages as Azure Data Factory activities.

可以通过 SSMS 将 AzureDTExec 配置为使用在数据工厂中生成管道的 Azure Active Directory (Azure AD) 应用程序。AzureDTExec can be configured via SSMS to use an Azure Active Directory (Azure AD) application that generates pipelines in your data factory. 还可将其配置为访问存储包的文件系统、文件共享或 Azure 文件存储。It can also be configured to access file systems, file shares, or Azure Files where you store your packages. 根据提供给 AzureDTExec 的调用选项值,AzureDTExec 将生成并运行一个独特的数据工厂管道,其中包含“执行 SSIS 包”活动。Based on the values you give for its invocation options, AzureDTExec generates and runs a unique Data Factory pipeline with an Execute SSIS Package activity in it. 结合提供的相同选项值调用 AzureDTExec 会重新运行现有管道。Invoking AzureDTExec with the same values for its options reruns the existing pipeline.

先决条件Prerequisites

若要使用 AzureDTExec,请下载并安装最新版本的 SSMS(18.3 或更高版本)。To use AzureDTExec, download and install the latest version of SSMS, which is version 18.3 or later. 此网站下载它。Download it from this website.

配置 AzureDTExec 实用工具Configure the AzureDTExec utility

在本地计算机上安装 SSMS 会同时安装 AzureDTExec。Installing SSMS on your local machine also installs AzureDTExec. 若要配置 AzureDTExec 的设置,请使用“以管理员身份运行”选项启动 SSMS。 To configure its settings, start SSMS with the Run as administrator option. 然后选择“工具” > “迁移到 Azure” > “配置支持 Azure 的 DTExec”。 Then select Tools > Migrate to Azure > Configure Azure-enabled DTExec.

“配置支持 Azure 的 DTExec”菜单

如果使用管理特权执行此操作,将会打开一个“AzureDTExecConfig”窗口,并将设置写入 AzureDTExec.settings 文件中。 This action opens a AzureDTExecConfig window that needs to be opened with administrative privileges for it to write into the AzureDTExec.settings file. 如果不是以管理员身份运行 SSMS,将打开“用户帐户控制(UAC)”窗口。If you haven't run SSMS as an administrator, a User Account Control (UAC) window opens. 输入管理员密码以提升特权。Enter your admin password to elevate your privileges.

“配置支持 Azure 的 DTExec”设置

在“AzureDTExecConfig”窗口中,按如下所示输入配置设置: In the AzureDTExecConfig window, enter your configuration settings as follows:

  • ApplicationId:输入使用适当权限创建的 Azure AD 应用的唯一标识符,以在数据工厂中生成管道。ApplicationId: Enter the unique identifier of the Azure AD app that you create with the right permissions to generate pipelines in your data factory. 有关详细信息,请参阅通过 Azure 门户创建 Azure AD 应用和服务主体For more information, see Create an Azure AD app and service principal via Azure portal.
  • AuthenticationKey:输入 Azure AD 应用的身份验证密钥。AuthenticationKey: Enter the authentication key for your Azure AD app.
  • TenantId:输入在其下创建了 Azure AD 应用的 Azure AD 租户的唯一标识符。TenantId: Enter the unique identifier of the Azure AD tenant, under which your Azure AD app is created.
  • DataFactory:输入数据工厂的名称,其中包含“执行 SSIS 包”活动的独特管道是基于调用 AzureDTExec 时提供的选项值生成的。DataFactory: Enter the name of your data factory in which unique pipelines with Execute SSIS Package activity in them are generated based on the values of options provided when you invoke AzureDTExec.
  • IRName:输入数据工厂中 Azure-SSIS IR 的名称,调用 AzureDTExec 时,该 IR 上的通用命名约定 (UNC) 路径中指定的包将会运行。IRName: Enter the name of the Azure-SSIS IR in your data factory, on which the packages specified in their Universal Naming Convention (UNC) path will run when you invoke AzureDTExec.
  • PipelineNameHashStrLen:输入基于调用 AzureDTExec 时提供的选项值生成的哈希字符串的长度。PipelineNameHashStrLen: Enter the length of hash strings to be generated from the values of options you provide when you invoke AzureDTExec. 这些字符串用于构成在 Azure-SSIS IR 上运行包的数据工厂管道的唯一名称。The strings are used to form unique names for Data Factory pipelines that run your packages on the Azure-SSIS IR. 32 个字符的长度通常已足够。Usually a length of 32 characters is sufficient.
  • ResourceGroup:输入在其中创建了数据工厂的 Azure 资源组的名称。ResourceGroup: Enter the name of the Azure resource group in which your data factory was created.
  • SubscriptionId:输入在其下创建了数据工厂的 Azure 订阅的唯一标识符。SubscriptionId: Enter the unique identifier of the Azure subscription, under which your data factory was created.
  • LogAccessDomain:输入域凭据,以便在写入日志文件时访问 UNC 路径中的日志文件夹;当指定了 LogPathLogLevel 不是 null 时,此字段是必填的。LogAccessDomain: Enter the domain credential to access your log folder in its UNC path when you write log files, which is required when LogPath is specified and LogLevel isn't null.
  • LogAccessPassword:输入密码凭据,以便在写入日志文件时访问 UNC 路径中的日志文件夹;当指定了 LogPathLogLevel 不是 null 时,此字段是必填的。LogAccessPassword: Enter the password credential to access your log folder in its UNC path when you write log files, which is required when LogPath is specified and LogLevel isn't null.
  • LogAccessUserName:输入用户名凭据,以便在写入日志文件时访问 UNC 路径中的日志文件夹;当指定了 LogPathLogLevel 不是 null 时,此字段是必填的。LogAccessUserName: Enter the username credential to access your log folder in its UNC path when you write log files, which is required when LogPath is specified and LogLevel isn't null.
  • LogLevel:为 Azure-SSIS IR 上的包执行输入所选的日志记录范围,预定义的选项包括“null”、“Basic”、“Verbose”或“Performance”。 LogLevel: Enter the selected scope of logging from predefined null, Basic, Verbose, or Performance options for your package executions on the Azure-SSIS IR.
  • LogPath:输入日志文件夹的 UNC 路径,在 Azure-SSIS IR 上执行包时生成的日志文件将写入其中。LogPath: Enter the UNC path of the log folder, into which log files from your package executions on the Azure-SSIS IR are written.
  • PackageAccessDomain:输入域凭据,以访问调用 AzureDTExec 时指定的 UNC 路径中的包。PackageAccessDomain: Enter the domain credential to access your packages in their UNC path that's specified when you invoke AzureDTExec.
  • PackageAccessPassword:输入密码凭据,以访问调用 AzureDTExec 时指定的 UNC 路径中的包。PackageAccessPassword: Enter the password credential to access your packages in their UNC path that's specified when you invoke AzureDTExec.
  • PackageAccessUserName:输入用户名凭据,以访问调用 AzureDTExec 时指定的 UNC 路径中的包。PackageAccessUserName: Enter the username credential to access your packages in their UNC path that's specified when you invoke AzureDTExec.

若要将包和日志文件存储在本地的文件系统或文件共享中,请将 Azure-SSIS IR 加入到与本地网络连接的虚拟网络,使 IR 能够提取包并写入日志文件。To store your packages and log files in file systems or file shares on-premises, join your Azure-SSIS IR to a virtual network connected to your on-premises network so that it can fetch your packages and write your log files. 有关详细信息,请参阅将 Azure-SSIS IR 加入虚拟网络For more information, see Join an Azure-SSIS IR to a virtual network.

若要避免以纯文本显示写入 AzureDTExec.settings 的敏感值,请将其编码为 Base64 编码的字符串。To avoid showing sensitive values written into the AzureDTExec.settings file in plain text, we encode them into strings of Base64 encoding. 调用 AzureDTExec 时,所有 Base64 编码的字符串将解码回到原始值。When you invoke AzureDTExec, all Base64-encoded strings are decoded back into their original values. 可以通过限制能够访问 AzureDTExec 文件的帐户来进一步保护该文件。You can further secure the AzureDTExec.settings file by limiting the accounts that can access it.

调用 AzureDTExec 实用工具Invoke the AzureDTExec utility

可以在命令行提示符下调用 AzureDTExec,并为用例方案中的特定选项提供相关值。You can invoke AzureDTExec at the command-line prompt and provide the relevant values for specific options in your use-case scenario.

该实用工具安装在 {SSMS Folder}\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn 中。The utility is installed at {SSMS Folder}\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn. 可将其路径添加到“PATH”环境变量,以便能够从任意位置调用它。You can add its path to the 'PATH' environment variable for it to be invoked from anywhere.

> cd "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn"
> AzureDTExec.exe  ^
  /F \\MyStorageAccount.file.core.chinacloudapi.cn\MyFileShare\MyPackage.dtsx  ^
  /Conf \\MyStorageAccount.file.core.chinacloudapi.cn\MyFileShare\MyConfig.dtsConfig  ^
  /Conn "MyConnectionManager;Data Source=MyDatabaseServer.database.chinacloudapi.cn;User ID=MyAdminUsername;Password=MyAdminPassword;Initial Catalog=MyDatabase"  ^
  /Set \package.variables[MyVariable].Value;MyValue  ^
  /De MyEncryptionPassword

调用 AzureDTExec 时可用的选项类似于调用 dtexec 时可用的选项。Invoking AzureDTExec offers similar options as invoking dtexec. 有关详细信息,请参阅 dtexec 实用工具For more information, see dtexec Utility. 目前支持以下选项:Here are the options that are currently supported:

  • /F[ile] :加载存储在文件系统、文件共享或 Azure 文件存储中的包。/F[ile]: Loads a package that's stored in file system, file share, or Azure Files. 可以指定包文件在文件系统、文件共享或 Azure 文件存储中的 UNC 路径及其 .dtsx 扩展名,作为此选项的值。As the value for this option, you can specify the UNC path for your package file in file system, file share, or Azure Files with its .dtsx extension. 如果指定的 UNC 路径包含空格,请用引号括住整个路径。If the UNC path specified contains any space, put quotation marks around the whole path.
  • /Conf[igFile] :指定要从中提取值的配置文件。/Conf[igFile]: Specifies a configuration file to extract values from. 使用此选项可为包设置不同的运行时配置(不同于设计时指定的配置)。Using this option, you can set a run-time configuration for your package that differs from the one specified at design time. 可以在 XML 配置文件中存储不同的设置,然后在执行包之前加载这些设置。You can store different settings in an XML configuration file and then load them before your package execution. 有关详细信息,请参阅 SSIS 包配置For more information, see SSIS package configurations. 若要指定此选项的值,请使用配置文件在文件系统、文件共享或 Azure 文件存储中的 UNC 路径及其 dtsConfig 扩展名。To specify the value for this option, use the UNC path for your configuration file in file system, file share, or Azure Files with its dtsConfig extension. 如果指定的 UNC 路径包含空格,请用引号括住整个路径。If the UNC path specified contains any space, put quotation marks around the whole path.
  • /Conn[ection] :为包中的现有连接管理器指定连接字符串。/Conn[ection]: Specifies connection strings for existing connection managers in your package. 使用此选项可为包中的现有连接管理器设置不同的运行时连接字符串(不同于设计时指定的连接字符串)。Using this option, you can set run-time connection strings for existing connection managers in your package that differ from the ones specified at design time. 按如下所示指定此选项的值:connection_manager_name_or_id;connection_string [[;connection_manager_name_or_id;connection_string]...]Specify the value for this option as follows: connection_manager_name_or_id;connection_string [[;connection_manager_name_or_id;connection_string]...].
  • /Set:重写包中参数、变量、属性、容器、日志提供程序、Foreach 枚举器或连接的配置。/Set: Overrides the configuration of a parameter, variable, property, container, log provider, Foreach enumerator, or connection in your package. 可以多次指定此选项。This option can be specified multiple times. 按如下所示指定此选项的值:property_path;valueSpecify the value for this option as follows: property_path;value. 例如,\package.variables[counter].Value;1counter 变量的值重写为 1。For example, \package.variables[counter].Value;1 overrides the value of counter variable as 1. 可以使用“包配置”向导来查找、复制和粘贴包中要重写其值的项的 property_path 值。 You can use the Package Configuration wizard to find, copy, and paste the value of property_path for items in your package whose value you want to override. 有关详细信息,请参阅包配置向导For more information, see Package Configuration wizard.
  • /De[crypt] :设置使用 EncryptAllWithPassword/EncryptSensitiveWithPassword 保护级别为包配置的解密密码。/De[crypt]: Sets the decryption password for your package that's configured with the EncryptAllWithPassword/EncryptSensitiveWithPassword protection level.

备注

使用新选项值( /De[cript] 选项除外)调用 AzureDTExec 会生成新的管道。Invoking AzureDTExec with new values for its options generates a new pipeline except for the option /De[cript].

后续步骤Next steps

在调用 AzureDTExec 时生成并运行包含“执行 SSIS 包”活动的唯一管道后,可以在数据工厂门户中监视这些管道。After unique pipelines with the Execute SSIS Package activity in them are generated and run when you invoke AzureDTExec, they can be monitored on the Data Factory portal. 如果要使用数据工厂来协调/计划这些管道,还可以向这些管道分配数据工厂触发器。You can also assign Data Factory triggers to them if you want to orchestrate/schedule them using Data Factory. 有关详细信息,请参阅将 SSIS 包作为数据工厂活动运行For more information, see Run SSIS packages as Data Factory activities.

警告

生成的管道预期仅由 AzureDTExec 使用。The generated pipeline is expected to be used only by AzureDTExec. 其属性或参数将来可能会更改,因此请不要对其进行修改,或将其重复用于任何其他目的。Its properties or parameters might change in the future, so don't modify or reuse them for any other purposes. 进行修改可能会破坏 AzureDTExec。Modifications might break AzureDTExec. 如果发生这种情况,请删除管道。If this happens, delete the pipeline. 下一次调用 AzureDTExec 时,它会生成新的管道。AzureDTExec generates a new pipeline the next time it's invoked.