教程:在 Azure 中部署和运行 SQL Server Integration Services (SSIS) 包Tutorial: Deploy and run a SQL Server Integration Services (SSIS) package in Azure

适用范围: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

本教程演示如何将 SQL Server Integration Services (SSIS) 项目部署到 Azure SQL 数据库中的 SSIS 目录、在 Azure-SSIS Integration Runtime 中运行包,以及监视正在运行的包。This tutorial shows you how to deploy a SQL Server Integration Services (SSIS) project to the SSIS Catalog in Azure SQL Database, run a package in the Azure-SSIS Integration Runtime, and monitor the running package.

必备条件Prerequisites

开始之前,请确保有 SQL Server Management Studio 版本 17.2 或更高版本。Before you start, make sure you have version 17.2 or later of SQL Server Management Studio. 若要下载 SSMS 最新版本,请参阅下载 SQL Server Management Studio (SSMS)To download the latest version of SSMS, see Download SQL Server Management Studio (SSMS).

另外确保已经在 Azure 中设置 SSISDB 数据库并预配了 Azure-SSIS Integration Runtime。Also make sure that you have set up the SSISDB database in Azure and provisioned the Azure-SSIS Integration Runtime. 有关如何在 Azure 上预配 SSIS 的信息,请参阅将 SQL Server Integration Services 包部署到 AzureFor info about how to provision SSIS on Azure, see Deploy SQL Server Integration Services packages to Azure.

对于 Azure SQL 数据库,请获取连接信息For Azure SQL Database, get the connection info

要在 Azure SQL 数据库上运行包,请获取连接到 SSIS 目录数据库 (SSISDB) 所需的连接信息。To run the package on Azure SQL Database, get the connection information you need to connect to the SSIS Catalog database (SSISDB). 在接下来的步骤中需要完全限定的服务器名称和登录信息。You need the fully qualified server name and login information in the procedures that follow.

  1. 登录到 Azure 门户Log in to the Azure portal.
  2. 从左侧的菜单选择“SQL 数据库”,然后选择“SQL 数据库”页中的 SSISDB 数据库 。Select SQL Databases from the left-hand menu, and then select the SSISDB database on the SQL databases page.
  3. 在数据库的“概述” 页上,查看完全限定的服务器名称。On the Overview page for your database, review the fully qualified server name. 若想查看“单击以复制”选项,将鼠标悬停在服务器名称上 。To see the Click to copy option, hover over the server name.
  4. 如果忘记了 Azure SQL 数据库服务器登录信息,导航到 SQL 数据库服务器页以查看服务器管理员名称。If you forget your Azure SQL Database server login information, navigate to the SQL Database server page to view the server admin name. 如有必要,可重置密码。You can reset the password if necessary.

连接到 SSISDB 数据库Connect to the SSISDB database

使用 SQL Server Management Studio 连接到 Azure SQL 数据库服务器上的 SSIS 目录。Use SQL Server Management Studio to connect to the SSIS Catalog on your Azure SQL Database server. 有关详细信息和屏幕截图,请参阅连接到 Azure 上的 SSISDB 目录数据库For more info and screenshots, see Connect to the SSISDB Catalog database on Azure.

请务必注意下面最重要的两点。Here are the two most important things to remember. 有关具体步骤,请参阅以下过程。These steps are described in the following procedure.

  • 采用格式“mysqldbserver.database.windows.net”,输入 Azure SQL 数据库服务器的完全限定的名称 。Enter the fully qualified name of the Azure SQL Database server in the format mysqldbserver.database.windows.net.
  • 选择 SSISDB 数据库以供连接。Select SSISDB as the database for the connection.

重要

Azure SQL 数据库服务器侦听端口 1433。An Azure SQL Database server listens on port 1433. 如果尝试从企业防火墙内连接到 Azure SQL 数据库服务器,必须在企业防火墙中打开该端口,才能成功连接。If you are attempting to connect to an Azure SQL Database server from within a corporate firewall, this port must be open in the corporate firewall for you to connect successfully.

  1. 打开 SQL Server Management Studio。Open SQL Server Management Studio.

  2. 连接到该服务器Connect to the server. 在“连接到服务器”对话框中,输入以下信息 :In the Connect to Server dialog box, enter the following information:

    设置Setting 建议的值Suggested value 描述Description
    服务器类型Server type 数据库引擎Database Engine 此值是必需的。This value is required.
    服务器名称Server name 完全限定的服务器名称The fully qualified server name 名称应采用此格式:mysqldbserver.database.windows.netThe name should be in this format: mysqldbserver.database.windows.net. 如果需要服务器名称,请参阅连接到 Azure 上的 SSISDB 目录数据库If you need the server name, see Connect to the SSISDB Catalog database on Azure.
    身份验证Authentication SQL Server 身份验证SQL Server Authentication 无法通过 Windows 身份验证连接到 Azure SQL 数据库。You can't connect to Azure SQL Database with Windows authentication.
    登录Login 服务器管理员帐户The server admin account 在创建服务器时指定的帐户。The account that you specified when you created the server.
    密码Password 服务器管理员帐户的密码The password for your server admin account 在创建服务器时指定的密码。The password that you specified when you created the server.
  3. 连接到 SSISDB 数据库Connect to the SSISDB database. 选择“选项” 展开“连接到服务器” 对话框。Select Options to expand the Connect to Server dialog box. 在展开的“连接到服务器” 对话框中,选择“连接属性” 选项卡。在“连接到数据库” 字段中,选择或输入 SSISDBIn the expanded Connect to Server dialog box, select the Connection Properties tab. In the Connect to database field, select or enter SSISDB.

  4. 然后选择“连接” 。Then select Connect. 对象资源管理器窗口随即在 SSMS 中打开。The Object Explorer window opens in SSMS.

  5. 在对象资源管理器中,展开“Integration Services 目录”,然后展开“SSISDB”,查看 SSIS 目录数据库中的对象 。In Object Explorer, expand Integration Services Catalogs and then expand SSISDB to view the objects in the SSIS Catalog database.

使用部署向导部署项目Deploy a project with the Deployment Wizard

若要详细了解如何部署包和部署向导,请参阅部署 Integration Services (SSIS) 项目和包Integration Services 部署向导To learn more about deploying packages and about the Deployment Wizard, see Deploy Integration Services (SSIS) Projects and Packages and Integration Services Deployment Wizard.

备注

若要部署到 Azure,只能使用项目部署模型。Deployment to Azure only supports the project deployment model.

启动 Integration Services 部署向导Start the Integration Services Deployment Wizard

  1. 在 SSMS 的对象资源管理器中,在展开“Integration Services 目录” 节点和“SSISDB” 节点后,展开一个项目文件夹。In Object Explorer in SSMS, with the Integration Services Catalogs node and the SSISDB node expanded, expand a project folder.

  2. 选择“项目” 节点。Select the Projects node.

  3. 右键单击“项目” 节点,选择“部署项目” 。Right-click on the Projects node and select Deploy project. Integration Services 部署向导随即打开。The Integration Services Deployment Wizard opens. 可以从 SSIS 目录数据库或从文件系统部署项目。You can deploy a project from an SSIS Catalog database or from the file system.

    通过 SSMS 部署项目

    此时,SSIS 部署向导对话框打开

使用部署向导部署项目Deploy a project with the Deployment Wizard

  1. 在部署向导的“简介” 页上查看简介。On the Introduction page of the Deployment Wizard, review the introduction. 选择“下一步” 打开“选择源” 页。Select Next to open the Select Source page.

  2. 在“选择源”页上,选择要部署的现有 SSIS 项目 。On the Select Source page, select the existing SSIS project to deploy.

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

    • 使用 <server_name>.database.windows.net 格式输入完全限定服务器名称。Enter the fully qualified server name in the format <server_name>.database.windows.net.
    • 提供身份验证信息,然后选择“连接” 。Provide authentication information, and then select Connect.
    • 然后选择“浏览” ,在 SSISDB 中选择目标文件夹。Then select Browse to select the target folder in SSISDB.
    • 再选择“下一步”打开“评审”页 。Then select Next to open the Review page. (仅当选择“连接”后,才会启用“下一步”按钮 。)(The Next button is enabled only after you select Connect.)
  4. 在“查看”页上,查看所选的设置 。On the Review page, review the settings you selected.

    • 可以通过选择“上一步” 或选择左窗格中的任意步骤来更改所做的选择。You can change your selections by selecting Previous, or by selecting any of the steps in the left pane.
    • 选择“部署” 启动部署过程。Select Deploy to start the deployment process.

    备注

    如果收到错误消息“没有任何活动工作代理。(.Net SqlClient 数据提供程序)”,请确保 Azure-SSIS Integration Runtime 正在运行 。If you get the error message There is no active worker agent. (.Net SqlClient Data Provider), make sure the Azure-SSIS Integration Runtime is running. 如果尝试在 Azure SSIS IR 处于停止状态时进行部署,则会发生此错误。This error occurs if you try to deploy while the Azure-SSIS IR is in a stopped state.

  5. 部署过程完成之后,“结果”页随即打开 。After the deployment process is complete, the Results page opens. 该页显示每个操作是成功了还是失败了。This page displays the success or failure of each action.

    • 如果操作失败,则选择“结果” 列中的“失败” 以显示错误说明。If the action failed, select Failed in the Result column to display an explanation of the error.
    • (可选)选择“保存报告...” 将结果保存到 XML 文件中。Optionally, select Save Report... to save the results to an XML file.
    • 选择“关闭” 退出该向导。Select Close to exit the wizard.

使用 PowerShell 部署项目Deploy a project with PowerShell

若要使用 PowerShell 将项目部署到 Azure SQL 数据库上的 SSISDB,请根据具体要求修改以下脚本。To deploy a project with PowerShell to SSISDB on Azure SQL Database, adapt the following script to your requirements. 此脚本枚举了 $ProjectFilePath 下的子文件夹以及每个子文件夹中的项目,然后在 SSISDB 中创建相同的文件夹,并将项目部署到这些文件夹。The script enumerates the child folders under $ProjectFilePath and the projects in each child folder, then creates the same folders in SSISDB and deploys the projects to those folders.

此脚本要求在运行脚本的计算机上安装 SQL Server Data Tools 版本 17.x 或 SQL Server Management Studio。This script requires SQL Server Data Tools version 17.x or SQL Server Management Studio installed on the computer where you run the script.

# Variables
$ProjectFilePath = "C:\<folder>"
$SSISDBServerEndpoint = "<servername>.database.windows.net"
$SSISDBServerAdminUserName = "<username>"
$SSISDBServerAdminPassword = "<password>"

# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=" + $SSISDBServerEndpoint + ";User ID="+ $SSISDBServerAdminUserName +";Password="+ $SSISDBServerAdminPassword + ";Initial Catalog=SSISDB"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

# Get the catalog
$catalog = $integrationServices.Catalogs['SSISDB']

write-host "Enumerating all folders..."

$folders = ls -Path $ProjectFilePath -Directory

if ($folders.Count -gt 0)
{
    foreach ($filefolder in $folders)
    {
        Write-Host "Creating Folder " $filefolder.Name " ..."

        # Create a new folder
        $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $filefolder.Name, "Folder description")
        $folder.Create()

        $projects = ls -Path $filefolder.FullName -File -Filter *.ispac
        if ($projects.Count -gt 0)
        {
            foreach($projectfile in $projects)
            {
                $projectfilename = $projectfile.Name.Replace(".ispac", "")
                Write-Host "Deploying " $projectfilename " project ..."

                # Read the project file, and deploy it to the folder
                [byte[]] $projectFileContent = [System.IO.File]::ReadAllBytes($projectfile.FullName)
                $folder.DeployProject($projectfilename, $projectFileContent)
            }
        }
    }
}

Write-Host "All done." 

运行包Run a package

  1. 在 SSMS 的对象资源管理器中,选择要运行的包。In Object Explorer in SSMS, select the package that you want to run.

  2. 右击并选择“执行” 打开“执行包” 对话框。Right-click and select Execute to open the Execute Package dialog box.

  3. 在“执行包” 对话框中,使用“参数” 、“连接管理器” 和“高级” 选项卡上的设置来配置包执行。In the Execute Package dialog box, configure the package execution by using the settings on the Parameters, Connection Managers, and Advanced tabs.

  4. 选择“确定” 运行包。Select OK to run the package.

在 SSMS 中监视正在运行的包Monitor the running package in SSMS

若要查看 Integration Services 服务器上当前运行的 Integration Services 操作的状态,比如部署、验证和包执行,请使用 SSMS 中的“活动操作” 对话框。To view the status of currently running Integration Services operations on the Integration Services server, such as deployment, validation, and package execution, use the Active Operations dialog box in SSMS. 若要打开“活动操作” 对话框,请右键单击“SSISDB” ,然后选择“活动操作” 。To open the Active Operations dialog box, right-click SSISDB, and then select Active Operations.

也可以在对象资源管理器中选择包,右击并选择“报表” ,然后选择“标准报表” 、“所有执行” 。You can also select a package in Object Explorer, right-click and select Reports, then Standard Reports, then All Executions.

有关如何在 SSMS 中监视正在运行的包的详细信息,请参阅监视正在运行的包和其他操作For more info about how to monitor running packages in SSMS, see Monitor Running Packages and Other Operations.

监视 SSIS 包活动的执行Monitor the Execute SSIS Package activity

如果使用“执行 SSIS 包”活动将包作为 Azure 数据工厂管道的一部分运行,则可以在数据工厂 UI 中监视管道运行。If you're running a package as part of an Azure Data Factory pipeline with the Execute SSIS Package activity, you can monitor the pipeline runs in the Data Factory UI. 然后可以从活动运行的输出中获取 SSISDB 执行 ID,并使用该 ID 在 SSMS 中查看更全面的执行日志和错误消息。Then you can get the SSISDB execution ID from the output of the activity run, and use the ID to check more comprehensive execution logs and error messages in SSMS.

在数据工厂中获取包执行 ID

监视 Azure-SSIS Integration RuntimeMonitor the Azure-SSIS Integration Runtime

若要获取有关运行有包的 Azure-SSIS Integration Runtime 的状态信息,请使用以下 PowerShell 命令。To get status info about the Azure-SSIS Integration Runtime in which packages are running, use the following PowerShell commands. 对于每个命令,请提供数据工厂、Azure-SSIS IR 和资源组的名称。For each of the commands, provide the names of the Data Factory, the Azure-SSIS IR, and the resource group.

有关详细信息,请参阅监视 Azure-SSIS Integration RuntimeFor more info, see Monitor Azure-SSIS integration runtime.

获取有关 Azure-SSIS Integration Runtime 的元数据Get metadata about the Azure-SSIS Integration Runtime

Get-AzDataFactoryV2IntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSsisIRName -ResourceGroupName $ResourceGroupName

获取 Azure-SSIS Integration Runtime 的状态Get the status of the Azure-SSIS Integration Runtime

Get-AzDataFactoryV2IntegrationRuntime -Status -DataFactoryName $DataFactoryName -Name $AzureSsisIRName -ResourceGroupName $ResourceGroupName

后续步骤Next steps