演练:将 SSIS 包作为 SQL 视图发布Walkthrough: Publish an SSIS Package as a SQL View

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是 Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factoryyes Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是 Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factoryyes Azure 数据工厂中的 SSIS Integration RuntimeSSIS Integration Runtime in Azure Data Factory

本演练提供在 SQL ServerSQL Server 数据库中将 SSIS 包作为 SQL 视图发布的详细步骤。This walkthrough provides detailed steps to publish an SSIS package as a SQL view in a SQL ServerSQL Server database.

必备条件Prerequisites

若要执行本演练,必须先在计算机上安装以下软件。You must have the following software installed on your computer to perform this walkthrough.

  1. 包含 SQL ServerSQL Server Integration ServicesIntegration ServicesSQL ServerSQL ServerSQL ServerSQL Server with SQL ServerSQL Server Integration ServicesIntegration Services.

  2. SQL Server Data ToolsSQL Server Data Tools.

步骤 1:生成 SSIS 项目并将其部署到 SSIS 目录Step 1: Build and Deploy SSIS Project to the SSIS Catalog

在此步骤中,创建一个从 SSIS 支持的数据源(在本示例中,我们使用 SQL ServerSQL Server 数据库)中提取数据并使用数据流目标组件输出数据的 SSIS 包。In this step, you create an SSIS package that extracts data from an SSIS supported data source - in this example, we use a SQL ServerSQL Server database - and outputs the data using a Data Streaming Destination component. 然后,将生成 SSIS 项目并将其部署到 SSIS 目录。Then you build and deploy the SSIS project to the SSIS catalog.

  1. 启动 SQL Server Data ToolsLaunch SQL Server Data Tools. “开始” 菜单上,依次指向 “所有程序”Microsoft SQL Server,再单击 SQL Server Data ToolsOn the Start menu, point to All Programs, point to Microsoft SQL Server, and click SQL Server Data Tools.

  2. 创建一个新的 Integration ServicesIntegration Services 项目。Create a new Integration ServicesIntegration Services project.

    1. 在菜单栏上单击“文件” ,指向“新建” ,然后单击“项目” 。Click File on the menu bar, point to New, and click Project.

    2. 在左窗格中展开“商业智能” ,然后在树视图中单击“Integration Services” 。Expand Business Intelligence in the left pane and click Integration Services in the tree view.

    3. 选择“Integration Services 项目”(如果尚未选中)。Select Integration Services Project if it is not already selected.

    4. 在“项目名称”中指定 SSISPackagePublishingSpecify SSISPackagePublishing for the project name.

    5. 指定项目的位置。Specify a location for the project.

    6. 单击“确定” 关闭“新建项目” 对话框。Click OK to close the New Project dialog box.

  3. 将“数据流” 组件从“SSIS 工具箱” 拖到“控制流” 选项卡的设计图面。Drag the Data Flow component from SSIS Toolbox to the design surface of the Control Flow tab.

  4. 在“控制流”中双击“数据流”组件打开“数据流设计器”。Double-click Data Flow component in the Control Flow to open Data Flow Designer.

  5. 源组件 从工具箱拖到“数据流设计器”,然后并将它配置为从数据源提取数据。Drag a source component from the toolbox to the Data Flow Designer and configure it to extract data from a data source.

    1. 为了进行演练,请创建一个包含表 Employee 的测试数据库 TestDBFor the purpose of the walkthrough, create a test database: TestDB with a table: Employee. 创建包含三列: IDFirstNameLastName 的表。Create the table with three columns, ID, FirstName and LastName.

    2. ID 设为主键。Set ID as a primary key.

    3. 插入两条包含以下数据的记录。Insert two records with the following data.

      IDID FIRSTNAMEFIRSTNAME LASTNAMELASTNAME
      11 JohnJohn DoeDoe
      22 JaneJane DoeDoe
    4. 将“OLE DB 源” 组件从“SSIS 工具箱” 拖到“数据流设计器” 。Drag the OLE DB Source component from the SSIS Toolbox on to the Data Flow Designer.

    5. 将组件配置为从 TestDB 数据库中的 Employee 表提取数据。Configure the component to extract data from the Employee table in the TestDB database. 为“OLE DB 连接管理器”选择“(local).TestDB”,为“数据访问模式”选择“表或视图”,为“表或视图的名称”选择“[dbo].[Employee]”。Select (local).TestDB for OLE DB connection manager, Table or view for Data access mode, and [dbo].[Employee] for Name of the table or the view.

      数据流目标 - OLE DB 连接Data Streaming Destination - OLE DB Connection

  6. 现在,将“数据流目标”从工具箱拖到数据流。Now, drag the Data Streaming Destination from the toolbox to the data flow. 应该可以在工具箱的“常用”部分中找到此组件。You should find this component in the Common section of the toolbox.

  7. 将数据流中的“OLE DB 源” 组件连接到“数据流目标” 组件。Connect the OLE DB Source component in the data flow to the Data Streaming Destination component.

  8. 生成 SSIS 项目并将其部署到 SSIS 目录。Build and deploy the SSIS project to SSIS Catalog.

    1. 在菜单栏上单击“项目” ,然后单击“部署” 。Click Project on the menu bar and click Deploy.

    2. 按照向导中的说明将项目部署到本地数据库服务器中的 SSIS 目录。Follow the instructions on the wizard to deploy the project to the SSIS Catalog in the local database server. 以下示例使用 Power BI 作为文件夹名称,使用 SSISPackagePublishing 作为 SSIS 目录中的项目名称。The following example uses Power BI as the folder name and SSISPackagePublishing as the project name in the SSIS catalog.

步骤 2:使用 SSIS 数据馈送发布向导将 SSIS 包发布为 SQL 视图Step 2: Use the SSIS Data Feed Publishing Wizard to Publish SSIS Package as a SQL View

在此步骤中,你将使用 SQL Server Integration Services (SSIS) 数据馈送发布向导将 SSIS 包发布为 SQL Server 数据库中的视图。In this step, you will use the SQL Server Integration Services (SSIS) Data Feed Publishing Wizard to publish the SSIS package as a view in a SQL Server database. 可通过查询此视图来使用包的输出数据。The output data of the package can be consumed by querying this view.

SSIS 数据馈送向导创建一个使用 OLE DB Provider for SSIS (SSISOLEDB) 的链接服务器,然后在链接服务器上创建一个包含查询的 SQL 视图。The SSIS Data Feed Publishing Wizard creates a linked server using the OLE DB Provider for SSIS (SSISOLEDB) and then creates a SQL view that consists of a query on the linked server. 该查询包括 SSIS 目录中的文件夹名称、项目名称和包名称。This query includes folder name, project name, and package name in the SSIS catalog.

在运行时,该视图通过你创建的链接服务器将查询发送到 OLE DB Provider for SSIS。At runtime, the view sends the query to the OLE DB Provider for SSIS via the linked server you created. OLE DB Provider for SSIS 执行你在查询中指定的包,然后向查询返回表格结果集。The OLE DB Provider for SSIS executes the package you specified in the query and returns the tabular result set to the query.

  1. 通过运行 C:\Program Files\Microsoft SQL Server\130\DTS\Binn 中的 ISDataFeedPublishingWizard.exe,或者单击“开始\所有程序”下的 Microsoft SQL Server 2016\SQL Server 2016 Data Feed Publishing Wizard,来启动 SSIS 数据馈送发布向导Launch SSIS Data Feed Publishing Wizard by running ISDataFeedPublishingWizard.exe from C:\Program Files\Microsoft SQL Server\130\DTS\Binn or by clicking Microsoft SQL Server 2016\SQL Server 2016 Data Feed Publishing Wizard under Start\All Programs.

  2. 在“简介”页上,单击“下一步”。Click Next on the Introduction page.

    数据馈送发布向导 -“简介”页Data Feed Publishing Wizard - Introduction Page

  3. 在“包设置”页上执行以下任务:On the Package Settings page, perform the following tasks:

    1. 键入包含 SSIS 目录的的 SQL Server 实例的 名称 ,或单击“浏览”选择服务器。Type the name of the SQL Server instance that contains the SSIS catalog or click Browse to select the server.

      数据馈送发布向导 -“包设置”页Data Feed Publishing Wizard - Package Settings Pag

    2. 单击“路径”字段旁边的“浏览” ,浏览 SSIS 目录,选择要发布的 SSIS 包(例如:SSISDB->SSISPackagePublishing->Package.dtsx),然后单击“确定” 。Click Browse next to Path field, browse the SSIS catalog, select the SSIS package you want to publish (for example: SSISDB->SSISPackagePublishing->Package.dtsx), and click OK.

      数据馈送发布向导 - 浏览包Data Feed Publishing Wizard - Browse for Package

    3. 使用页面底部的“包参数”、“项目参数”和“连接管理器”选项卡,输入包的任何包参数、项目参数或连接管理器设置的值。Using the Package Parameters, Project Parameters, and Connection Managers tabs at the bottom of the page, enter values for any package parameters, project parameters, or connection manager settings for the package. 还可以指定要用于包执行的环境引用,并将项目/包参数绑定到环境变量。You can also indicate an environment reference to be used for the package execution and bind project/package parameters to environment variables.

      我们建议你将敏感参数绑定到环境变量。We recommend that you bind sensitive parameters to environment variables. 这是为了确保不会以纯文本格式将敏感参数的值存储在向导创建的 SQL 视图中。This is to ensure that the value of a sensitive parameter is not stored in the plain text format in the SQL view created by the wizard.

    4. 单击“下一步” 切换“发布设置”页 。Click Next to switch the Publish Settings page.

  4. 在“发布设置”页上执行以下任务:On the Publish Settings page, perform the following tasks:

    1. 选择要创建的视图的 数据库Select the database for the view to be created.

      数据馈送发布向导 -“发布设置”页Data Feed Publishing Wizard - Publish Settings Pag

    2. 输入 视图名称Type a name for the view. 也可以从下拉列表中选择一个现有视图。You can also select an existing view from the drop-down list.

    3. 在“设置”列表中,指定要与视图关联的 链接服务器名称In the Settings list, specify a name of the linked server to be associated with the view. 如果链接服务器尚不存在,向导将在创建视图之前创建链接服务器。If linked server does not already exist, the wizard will create the linked server before creating the view. 还可以在此处设置 User32BitRuntimeTimeout 的值。You can also set values for User32BitRuntime and Timeout values here.

    4. 单击“高级”按钮。Click the Advanced button. 你应会看到“高级设置”对话框。You should see the Advanced Settings dialog box.

    5. 在“高级设置”对话框中执行以下操作:On the Advanced Settings dialog box, do the following:

      1. 指定要在其中创建视图的数据库架构(“架构”字段)。Specify the database schema in which you want the view to be created (Schema field).

      2. 指定在通过网络发送数据之前是否应当将其加密(“加密”字段)。Specify whether data should be encrypted before sending it over the network (Encrypt field). 有关此设置和 TrustServerCertificate 设置的更多详细信息,请参阅 Using Encryption Without Validation (在不验证的情况下使用加密)主题。See Using Encryption Without Validation topic for more details about this setting and the TrustServerCertificate setting.

      3. 指定在启用加密设置时是否可以使用自签名服务器证书(TrustServerCertificate 字段)。Specify whether a self-signed server certificate can be used when the encryption setting is enabled (TrustServerCertificate field).

      4. 单击“确定” 关闭“高级设置” 对话框。Click OK to close the Advanced Settings dialog box.

    6. 单击“下一步” 切换到“验证”页 。Click Next to switch to the Validation page.

  5. 在“验证”页上,检查验证所有设置的值后返回的结果。On the Validation page, review the results from the validating the values for all the settings. 在以下示例中,你将看到针对链接服务器存在状态的 警告 ,因为选定的 SQL Server 实例上不存在链接服务器。In the following example, you see a warning on the existence of linked server because the linked server does not exist on the selected SQL Server instance. 如果你看到“结果”中包含“错误”,请将鼠标悬停在“错误”,这样便可以查看有关该错误的详细信息。If you see Error for Result, hover the mouse over Error and you will see the details about the error. 例如,如果你尚未启用“允许对 SSISOLEDB 提供程序使用 inprocess 选项”,则在执行链接服务器配置操作时会收到错误。For example, if you had not enabled the Allow inprocess option for the SSISOLEDB provider, you will get an error on the Configuration of Linked Server action.

    数据馈送发布向导 -“验证”页Data Feed Publishing Wizard - Validation Page

  6. 若要将此报告保存为 XML 文件,请单击“保存报告”。To save this report as an XML file, click Save Report.

  7. 在“验证”页上单击“下一步”切换到“摘要”页。Click Next on the Validation page to switch to the Summary page.

  8. 在“摘要” 页中检查所做的选择,然后单击“发布” 启动发布过程。这将会创建链接服务器(如果服务器上没有链接服务器),然后使用链接服务器创建视图。Review your selection in the Summary page and click Publish to start the publishing process, which will create the linked server if it does not exist already on the server and then create the view using the linked server.

    数据馈送发布向导 -“摘要”页Data Feed Publishing Wizard - Summary Page

    现在,可以针对 TestDB 数据库执行以下 SQL 语句,来查询包的输出数据:SELECT * FROM [SSISPackageView]。The output data of the package can now be queried by executing the following SQL statement against the TestDB database: SELECT * FROM [SSISPackageView].

  9. 若要将此报告保存为 XML 文件,请单击“保存报告”。To save this report as an XML file, click Save Report.

  10. 查看发布过程的结果,然后单击“完成”关闭向导。Review the results from the publishing process and click Finish to close the wizard.

    备注

    不支持以下数据类型:text、ntext、image、nvarchar(max)、varchar(max) 和 varbinary(max)。The following data types are not supported : text, ntext, image, nvarchar(max), varchar(max), and varbinary(max).

步骤 3:测试 SQL 视图Step 3: Test the SQL view

在此步骤中,你将运行 SSIS 数据馈送发布向导创建的 SQL 视图。In this, you will run the SQL view created by the SSIS Data Feed Publishing Wizard.

  1. 启动 SQL Server Management Studio。Launch SQL Server Management Studio.

  2. 展开 <machine name>、“数据库”、<database you selected in the wizard> 和“视图” 。Expand <machine name>, Databases, <database you selected in the wizard>, and Views.

  3. 右键单击向导创建的 <view created by the wizard>,然后单击“选择前 1000 行”。Right-click the <view created by the wizard> created by the wizard and click Select top 1000 rows.

  4. 确认能够看到 SSIS 包的结果。Confirm that you see results from the SSIS package.

步骤 4:验证 SSIS 包执行Step 4: Verify the SSIS Package Execution

在此步骤中,你将验证是否已执行 SSIS 包。In this step, you will verify that the SSIS package was executed.

  1. 在 SQL Server Management Studio 中,依次展开“Integration Services 目录” 、“SSISDB” 、包含 SSIS 项目的 文件夹、“项目” 、项目节点、“包” 。In SQL Server Management Studio, expand Integration Services Catalogs, expand SSISDB, expand folder in which your SSIS project exists, expand Projects, expand your project node, and expand Packages.

  2. 右键单击 SSIS 包,指向“报告” ,指向“标准报告” ,然后单击“所有执行” 。Right-click on the SSIS package, and click point to Reports, point to Standard Reports, and click All Executions.

  3. 你应会报告中看到 SSIS 包的执行状态。You should see the SSIS package execution in the report.

    备注

    在 Windows Vista Service Pack 2 计算机上,你可能会在报告中看到两个 SSIS 包执行状态,一个成功,一个失败。On a Windows Vista Service Pack 2 computer, you may see two SSIS package executions in the report, a successful one and a failed one. 忽略失败的执行,因为它是由此版本中的已知问题造成的。Ignore the failed one as it is caused by a known issue in this release.

更多信息More info

数据馈送发布向导将执行以下重要步骤:The Data Feed Publish Wizard performs the following important steps:

  1. 创建链接服务器,并将其配置为使用 OLE DB Provider for SSIS。Creates a linked server and configures it to use the OLE DB Provider for SSIS.

  2. 在指定的数据库中创建 SQL 视图,该视图将查询包含所选包的目录信息的链接服务器。Creates a SQL view in the specified database, which queries the linked server with catalog information for the selected package.

本部分提供在不使用数据馈送发布向导的情况下,创建链接服务器和 SQL 视图的过程。This section has procedures for creating a linked server and a SQL view without using the Data Feed Publish Wizard. 此外,还提供有关将 OPENQUERY 函数与 OLE DB Provider for SSIS 配合使用的其他信息。It also has additional information about using the OPENQUERY function with the OLE DB Provider for SSIS.

使用 OLE DB Provider for SSIS 创建链接服务器Create a Linked Server using the OLE DB Provider for SSIS

在 SQL Server Management Studio 中,通过运行以下查询来使用 OLE DB Provider for SSIS (SSISOLEDB) 创建链接服务器。Create a linked server using the OLE DB Provider for SSIS (SSISOLEDB) by running the following query in SQL Server Management Studio.

  
USE [master]  
GO  
  
EXEC sp_addlinkedserver  
@server = N'SSISFeedServer',  
@srvproduct = N'Microsoft',  
@provider = N'SSISOLEDB',  
@datasrc = N'.'  
GO  
  

使用链接服务器和 SSIS 目录信息创建视图Create a View using Linked Server and SSIS Catalog Information

在此步骤中,你将创建一个针对你在上一部分中创建的链接服务器运行查询的 SQL 视图。In this step, you will create a SQL view that runs a query on the linked server you created in the previous section. 该查询将包括 SSIS 目录中的文件夹名称、项目名称和包名称。The query will include folder name, project name, and package name in the SSIS Catalog.

在运行时执行该视图时,视图中定义的链接服务器查询将启动查询中指定的 SSIS 包,并接收表格结果集形式的包输出。At runtime, when the view is executed, the linked server query that is defined in the view starts the SSIS package specified in the query and receives the package output as a tabular result set.

  1. 在创建视图之前,请在新查询窗口中键入并运行以下查询。Before creating the view, type and run the following query in the new query window. OPENQUERY 是 SQL Server 支持的行集函数。OPENQUERY is a rowset function supported by SQL Server. 它使用与链接服务器关联的 OLE DB 提供程序在指定的链接服务器上执行指定的传递查询。It executes the specified pass-through query on the specified linked server using the OLE DB Provider associated with the linked server. OPENQUERY 可以在查询的 FROM 子句中引用,就好象它是一个表名。OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. 有关详细信息,请参阅 MSDN 库上的 OPENQUERY 文档See OPENQUERY documentation on MSDN Library for more information.

    SELECT * FROM OPENQUERY(SSISFeedServer,N'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')   
    GO  
    

    重要

    如果需要,请更新文件夹名称、项目名称和包名称。Update folder name, project name, and package name if needed. 如果 OPENQUERY 函数失败,请在 SQL Server Management Studio 中依次展开“服务器对象” 、“链接服务器” 、“提供程序” ,然后双击 SSISOLEDB 提供程序,并确保“允许 inprocess” 选项处于启用状态。If the OPENQUERY function fails, in the SQL Server Management Studio, expand Server Objects, expand Linked Servers, expand Providers, and double click SSISOLEDB provider, and ensure that the Allow inprocess option is enabled.

  2. 通过运行以下查询,在数据库 TestDB 中为本演练创建一个视图。Create a view in the database TestDB for the purpose of this walkthrough) by running the following query.

    
    USE [TestDB]   
    GO   
    
    CREATE VIEW SSISPackageView AS   
    SELECT * FROM OPENQUERY(SSISFeedServer, 'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')   
    GO  
    
    
  3. 通过运行以下查询来测试该视图。Test the view by running the following query.

    SELECT * FROM SSISPackageView  
    

OPENQUERY 函数OPENQUERY Function

OPENQUERY 函数的语法是:The syntax for OPENQUERY function is:

SELECT * FROM OPENQUERY(<LinkedServer Name>, N'Folder=<Folder Name from SSIS Catalog>; Project=<SSIS Project Name>; Package=<SSIS Package Name>; Use32BitRuntime=[True | False];Parameters="<parameter_name_1>=<value1>; parameter_name_2=<value2>";Timeout=<Number of Seconds>;')  

Folder、Project 和 Package 参数是必需的。Folder, Project, and Package parameters are mandatory. Use32BitRuntime、Timeout 和 Parameters 是可选的。Use32BitRuntime, Timeout and Parameters are optional.

Use32BitRuntime 的值可以是 0、1、true 或 false。The value of Use32BitRuntime can be 0, 1, true, or false. 它指示当 SQL Server 的平台是 64 位时,包是否应使用 32 位运行时(1 或 true)运行。It indicates whether the package should run with 32-bit runtime (1 or true) when the platform of SQL Server is 64-bit.

Timeout 指示在 SSIS 包中的新数据到达之前,OLE DB Provider for SSIS 可以等待的秒数。Timeout indicates the number of seconds that the OLE DB provider for SSIS can wait before new data arrives from the SSIS package. 默认情况下,超时为 60 秒。By default, the timeout is 60 seconds. 可以指定介于 20 和 32000 之间的整数超时值。You can specify an integer value for the timeout between 20 and 32000.

Parameters 包含包参数和项目参数的值。Parameters contain the value of both package parameters and project parameters. 参数的规则与 DTExec中的参数相同。The rules for parameters are same as parameters in DTExec.

以下列表指定了查询子句中允许的特殊字符:The following list specifies the special characters allowed in the query clause:

  • 单引号 (') - 标准 OPENQUERY 支持此字符。Single Quote (') - This is supported by the standard OPENQUERY. 如果你想要在查询子句中使用单引号,请使用两个单引号 ('')。If you want to use the single quote in the query clause, use two single quotes ('').

  • 双引号 (") - 查询的参数部分括在双引号中。Double-Quote (") - The parameters part of the query is enclosed in double-quotes. 如果参数值本身包含双引号,请使用转义符。If a parameter value itself contains a double-quote, use the escape character. 例如:"。For example: ".

  • 左右方括号([ 和 ])- 这些字符用于指示首部/尾部空格。Left and right square brackets ([ and ]) - These characters are used to indicate leading/rear spaces. 例如,“[ some spaces ]”表示字符串“ some spaces ”的首部和尾部各有一个空格。For example, "[ some spaces ]" represents the string " some spaces " with one leading space and one trailing space. 如果在查询子句中使用这些字符本身,必须将其转义。If these characters themselves are used in the query clause, they must be escaped. 例如, \[ 和 \]。For example: \[ and \].

  • 正斜杠 (\) - 查询子句中使用的每个 \ 必须使用转义符。Forward Slash (\) - Every \ used in the query clause must use escape character. 例如,查询子句中的 \\ 将作为 \ 计算。For example, \\ is evaluated as \ in the query clause.

另请参阅See Also

数据流目标 Data Streaming Destination
配置数据流目标Configure Data Streaming Destination