ssis导入xml_SSIS包中的XML任务概述

ssis导入xml

介绍 (Introduction)

We can use an SSIS package to perform various tasks such as data import, transform, and get output in various formats. Once we launch Visual Studio 2019 and create an integration services project, it shows you various tasks in the SSIS toolbox. In this article, we will explore the XML Task in the SSIS.

我们可以使用SSIS包执行各种任务，例如数据导入，转换和获取各种格式的输出。 启动Visual Studio 2019并创建集成服务项目后，它将在SSIS工具箱中向您显示各种任务。 在本文中，我们将探讨SSIS中的XML任务 。

XML任务概述 (Overview of an XML Task)

We use XML Task to validate, compare the XML documents, apply various transformations. It uses Extensible Stylesheet Language Transformations (XSLT) for applying the transformation on the XML data. We can also merge XML documents from various sources and create a comprehensive XML document.

我们使用XML Task来验证，比较XML文档，应用各种转换。 它使用可扩展样式表语言转换（XSLT）将转换应用于XML数据。 我们还可以合并来自各种来源的XML文档，并创建一个全面的XML文档。

探索XML任务配置 (Explore XML Task configurations)

Let’s drag this XML task in the Control flow and explore its various configurations.

让我们将此XML任务拖到Control流中，并探索其各种配置。

Now, double-click on the XML editor to open the XML task editor.

现在，双击XML编辑器以打开XML任务编辑器。

Let’s have a look at the options available in the task editor:

让我们看一下任务编辑器中可用的选项：

• Operation type: It provides a list of options that we can perform on the XML document. We get in the Input section 操作类型：它提供了我们可以在XML文档上执行的选项的列表。 我们进入输入部分
  • Diff: We can compare two XML documents using the diff operation. It uses the first XML as source XML and compares it with the following XML specified. In the output, we get an XML Diffigram document ：我们可以使用差异操作比较两个XML文档。 它使用第一个XML作为源XML，并将其与指定的以下XML进行比较。 在输出中，我们得到一个XML Diffigram文档
  • Merge: Sometimes, we require to merge two copies of XML documents in a single document. Similar to the diff operation, it uses the first XML as a base document. It merges the second XML document with the base XML 合并：有时，我们需要在一个文档中合并XML文档的两个副本。 与diff操作类似，它使用第一个XML作为基础文档。 它将第二个XML文档与基本XML合并
  • Patch: We can use the Patch method to create a new XML document that includes the output of the XML Diffigram document 补丁：我们可以使用补丁方法创建一个新的XML文档，其中包括XML Diffigram文档的输出
  • Validate: We use an XML Schema definition(XSD) to validate an XML document. An XSD document contains the tabs, attributes, definition of the XML document. We can validate the XML using this option 验证：我们使用XML架构定义（XSD）来验证XML文档。 XSD文档包含XML文档的选项卡，属性和定义。 我们可以使用此选项来验证XML
  • XPATH: We can perform XPath queries o XML documents using this XPATH option XPATH：我们可以使用此XPATH选项执行XPath查询或XML文档
  • XSLT: We can transform an XSL document using the XML documents XSLT：我们可以使用XML文档来转换XSL文档

By default, it shows the XML editor in the Diff operation mode. For the Diff operation type, we have further options in the Diff Option group.

默认情况下，它以Diff操作模式显示XML编辑器。 对于“差异”操作类型，“ 差异选项”组中还有其他选项。

Expand the DiffOptions to check various options for Diff.

展开DiffOptions以检查Diff的各种选项。

• DiffAlgorithm: It defines the comparison algorithm for the XML document. We have Auto, Fast and Precise modes. By default, it uses the Auto mode. In the precise mode, XML comparison might take a longer duration. Auto mode selects the mode depending upon the document size. DiffAlgorithm：它定义XML文档的比较算法。 我们有自动，快速和精确模式。 默认情况下，它使用自动模式。 在精确模式下，XML比较可能需要更长的时间。 自动模式根据文档尺寸选择模式。
• DiffOptions: By default, all options in this group are DiffOptions：默认情况下，该组中的所有选项均为false: false ：
  • IgnoreComments: If this value is IgnoreComments：如果此值为True, SSIS compares the comments as well in both XML’s True ，则SSIS还将在两个XML的注释中也比较注释
  • IgnoreNamespaces: It defines the comparison for the element uniform resource identifier(URI and its attributes. If we set this value to TRUE, XML elements with the same local name are considered as identical. It ignores the namespace value for both the elements IgnoreNamespaces：定义元素统一资源标识符（URI及其属性）的比较。如果将此值设置为TRUE，则具有相同本地名称的XML元素将被视为相同。它将忽略两个元素的名称空间值
  • IgnorePrefixes: It shows whether the XML task compares the element and its attributes. It ignores the prefix while making the comparison
  • IgnorePrefixes：它显示XML任务是否比较元素及其属性。 进行比较时会忽略前缀
  • IgnoreXMLDeclaration: It ignores the XML declarations in the documents for comparison purpose IgnoreXMLDeclaration：出于比较目的，它将忽略文档中的XML声明
  • IgnoreOrderOfChildElements: It ignores the order of the child elements in the XML document. If we set this property to TRUE, child element with the different positions is considered to be identical
  • IgnoreOrderOfChildElements：它忽略XML文档中子元素的顺序。 如果我们将此属性设置为TRUE，则具有不同位置的子元素被视为相同
  • IgnoreWhiteSpaces: It controls the white space while making the comparison IgnoreWhiteSpaces：在进行比较时控制空白
  • IgnoreProcessingInstructions: We can specify whether we want to compare the processing instructions or not IgnoreProcessingInstructions：我们可以指定是否要比较处理指令
  • IgnoreDTD: It defines whether we want to compare the document type declaration(DTD) or not IgnoreDTD：它定义我们是否要比较文档类型声明（DTD）
• FailOnDifference: If the XML task finds any difference in both the base and second XML document, we can control the SSIS package behaviors FailOnDifference：如果XML任务在基本XML文档和第二个XML文档中发现任何差异，我们可以控制SSIS包的行为
  • FailOnDifference= True: Package fails if any difference exists FailOnDifference = True：如果存在任何差异，程序包将失败
  • FailOnDifference=False: The package does not fail due to the XML document’s difference FailOnDifference = False：包不会因XML文档的不同而失败
• SaveDiffGram: Once the XML document compares the XML document, it creates a DiffGram file, if we set this property to TRUE. We also need to define a location where this SaveDiffGram ：XML文档比较XML文档后，如果我们将此属性设置为TRUE，它将创建一个DiffGram文件。 我们还需要定义此DiffGram file is saved. We can store the difference in a variable as well DiffGram文件的保存位置。 我们也可以将差异存储在变量中

使用XML任务比较SSIS包中的XML文档 (Compare XML documents in a SSIS package using XML task)

Let’s create two XML documents and compare them using this XML task in the SSIS package. For the demonstration purpose, I use the XML specified in the Microsoft docs.

让我们创建两个XML文档，并使用SSIS包中的XML任务比较它们。 出于演示目的，我使用Microsoft文档中指定的XML 。

Base file:

基本文件：

Second XML File:

第二个XML文件：

In the source type, create a new file connection and specify the location of the XML document we want to use as a base file.

在源类型中，创建一个新的文件连接，并指定我们要用作基本文件的XML文档的位置。

Similarly, we need to provide the path of the second XML file in the Second Operand group. Select the SecondOperandTYpe as a File Connection. We can use the option DirectInput or variable as well from the drop-down menu.

同样，我们需要在Second Operand组中提供第二个XML文件的路径。 选择SecondOperandTYpe作为文件连接。 我们也可以从下拉菜单中使用选项DirectInput或变量。

Additionally, we need to configure the path for the DiffGram file that captures the difference in both XML documents. First, set the property SaveDiffGram to True. In the new file connection manager, select the option to create the file and specify the file name and directory.

另外，我们需要为DiffGram文件配置路径，以捕获两个XML文档中的差异。 首先，将属性SaveDiffGram设置为True。 在新的文件连接管理器中，选择选项以创建文件并指定文件名和目录。

We do not want our SSIS package to fill in case it finds any difference in both XML’s file. I would go with the default setting FailOnDifference as False. You can change it to True in case you want the package to fail in case of differences.

我们不希望我们的SSIS包填充以防万一它发现两个XML文件中的任何差异。 我将默认设置FailOnDifference设置为False。 如果希望软件包因差异而失败，可以将其更改为True。

In the connection managers, we can see the base XML, second XML file along with the DiffGram file to capture the differences.

在连接管理器中，我们可以看到基本XML，第二个XML文件以及DiffGram文件，以捕获差异。

Click on Start to execute the SSIS Package. We can see the package is successful because we have selected the property FailOnDifference as False.

单击开始以执行SSIS包。 我们可以看到包成功了，因为我们选择了FailOnDifference属性为False。

Let’s open the DiffGram file from the directory we specified during configuration. In the base and source file, we have additional data for the book id 102. It does not exist in the base file, so you got the difference in the output.

让我们从配置过程中指定的目录中打开DiffGram文件。 在基础文件和源文件中，我们还有书ID 102的其他数据。该文件在基础文件中不存在，因此您得到了输出上的差异。

We have book id 101 in both the XML documents. Let’s change the content for book id 101 in the base XML.

在两个XML文档中，我们都有书ID 101。 让我们在基本XML中更改书籍ID 101的内容。

Save this XML file and rerun the package. This time we get a failure for the SSIS package. Click on the Execution results, and we see that it failed because the DiffGram file already exists.

保存此XML文件并重新运行程序包。 这次我们的SSIS包失败了。 单击执行结果，我们看到它失败，因为DiffGram文件已经存在。

Go back to the control flow and open the XML task editor. In the output, we have the option to overwrite the output file if it already exists. Switch the value for OverWriteDestination as True.

返回控制流并打开XML任务编辑器。 在输出中，我们可以选择覆盖输出文件（如果已经存在）。 将OverWriteDestination的值切换为True。

Click Ok and execute the package. The package runs fine this time. Open the DiffGram file and note the difference in both XML’s. You can see here that it highlights the difference if it exists in an element value as well.

单击确定，然后执行该程序包。 这次包运行良好。 打开DiffGram文件，并注意两种XML的区别。 您可以在此处看到，如果元素值中也存在差异，则会突出显示差异。

在SSIS包中合并使用XML任务的XML (Merge XML’s using XML Task in SSIS packages)

In the previous section, we compared the XML document using the diff operation of the XML task. Let’s use the merge operation. As highlighted earlier, merge operations add the content of the second document into the base document.

在上一节中，我们使用XML任务的diff操作比较了XML文档。 让我们使用合并操作。 如前所述，合并操作将第二个文档的内容添加到基本文档中。

Let’s open the XML task editor and change the operation type to Merge. It changes the configuration options corresponding to the merge operation.

让我们打开XML任务编辑器，并将操作类型更改为Merge。 它更改与合并操作相对应的配置选项。

Before we go further, let’s view the XML documents we want to merge into a new file. Here, the left file is the base XML(1), and Right-side XML is the XML we wish to merge with the base file.

在继续之前，让我们查看要合并到新文件中的XML文档。 在这里，左侧文件是基础XML（1），右侧XML是我们希望与基础文件合并的XML。

Let’s make the following changes in the merge file editor.

让我们在合并文件编辑器中进行以下更改。

1. Specify the path of the base XML file
   指定基本XML文件的路径
2. Specify the path of the file we wish to merge in the base file
   指定我们希望在基本文件中合并的文件的路径
3. 1. XPathStringSourceType: Direct Input
      XPathStringSourceType：直接输入
   2. XPatStringSource: It is the tags that we used in the XML file. If we look at both XML documents, it is embedded in the tags <catalog>. You need to specify this tag in this configuration, as shown above
      XPatStringSource：这是我们在XML文件中使用的标签。 如果我们看两个XML文档，它都嵌入在标签<catalog>中。 您需要在此配置中指定此标签，如上所示
4. Specify the file name and destination for the new merge file. We can also choose to overwrite the existing file if it already exists
   指定新合并文件的文件名和目的地。 我们也可以选择覆盖现有文件（如果已经存在）

Once we have done the configuration for merge operation, click Ok and execute the SSIS package.

完成合并操作的配置后，单击“确定”并执行SSIS包。

Package execution is successful. Let’s open the merge XML file and see if it merged the following XML into the base XML. In the following screenshot, we view the XML content into the base XML document.

包执行成功。 让我们打开合并XML文件，看看它是否将以下XML合并为基本XML。 在下面的屏幕快照中，我们将XML内容查看到基本XML文档中。

结论 (Conclusion)

In this article, we learned about the XML task in the SSIS package. We explored the XML files comparison and merge operations using the Diff and Merge operations. You can also explore other XML operations such as Patch, validate, XPath and XSLT.

在本文中，我们了解了SSIS包中的XML任务。 我们探讨了使用Diff和Merge操作进行XML文件比较和合并操作。 您还可以探索其他XML操作，例如Patch，validate，XPath和XSLT。

翻译自: https://www.sqlshack.com/an-overview-of-the-xml-task-in-ssis-packages/

ssis导入xml