ssis导入xml

Until now, Microsoft has not included the XML Destination component in SQL Server Integration Services (SSIS). Many years ago, this component was requested on the Microsoft connect website, but it was closed as “Won’t fix.” For this reason, many workarounds and third-party components were created. In this article, we’ll talk about these components and some of the popular solutions for exporting data to XML using SSIS.

到目前为止，Microsoft尚未将XML Destination组件包含在SQL Server Integration Services（SSIS）中。 许多年前，Microsoft Connect网站上曾要求使用此组件，但由于“无法修复”而被关闭。 因此，创建了许多解决方法和第三方组件。 在本文中，我们将讨论这些组件以及使用SSIS将数据导出到XML的一些流行解决方案。

SSIS XML目标样本 (SSIS XML Destination sample)

Our first solution is a custom SSIS component created by the Microsoft SSIS team. It started in the SSIS Community Samples project on , and later migrated to .

我们的第一个解决方案是由Microsoft SSIS团队创建的自定义SSIS组件。 它始于上的SSIS社区示例项目，后来迁移到 。

As described in the readme file, it is a complex data flow task Destination component created using C#, and it includes features such as:

如自述文件中所述，它是使用C＃创建的复杂数据流任务Destination组件，并且具有以下功能：

• The Destination can be used with multiple inputs and merges the data from each into one Destination XML file
  目标可以与多个输入一起使用，并将每个输入的数据合并到一个目标XML文件中
• The file is created using a File Connection Manager
  使用文件连接管理器创建文件
• It has a custom user interface
  它具有自定义的用户界面

One main issue is that this component’s latest release is relevant to SQL Server 2012. If you are using a newer version of SQL Server, you might have to edit the code and replace the old SQL Server assemblies referenced by the newer version.

一个主要问题是该组件的最新版本与SQL Server 2012相关。如果您使用的是SQL Server的较新版本，则可能必须编辑代码并替换该较新版本引用的旧SQL Server程序集。

商业SSIS XML目标组件 (Commercial SSIS XML Destination components)

There are many third-party XML Destination components created by companies in this domain, such as:

该域中的公司创建了许多第三方XML Destination组件，例如：

KingswaySoft SSIS XML目标 (KingswaySoft SSIS XML Destination)

KingswaySoft provides a commercial XML Destination component that can be used to merge input data based on the XML data structure defined in the component. An XML document can be generated as a local file or can be sent to an HTTP URL to perform an XML or SOAP-based service call. For more information, refer to

KingswaySoft提供了一个商业XML Destination组件，该组件可用于基于组件中定义的XML数据结构合并输入数据。 XML文档可以作为本地文件生成，也可以发送到HTTP URL以执行基于XML或SOAP的服务调用。 有关更多信息，请参见

Keelio XML SSIS工具包 (Keelio XML SSIS Toolkit)

Keelio provides an XML Toolkit for SSIS that contains a component called Template Transformation, which can be used to generate XML documents. For more information, refer to

Keelio提供了一个用于SSIS的XML工具包，其中包含一个名为“模板转换”的组件，该组件可用于生成XML文档。 有关更多信息，请参考

ZappySys XML目标 (ZappySys XML Destination)

ZappySys provides an SSIS XML Destination component that can be used to export XML files from different data sources such as SQL Server, Oracle, MySQL, and others. For more information, refer to:

ZappySys提供了一个SSIS XML Destination组件，该组件可用于从不同的数据源（例如SQL Server，Oracle，MySQL等）导出XML文件。 有关更多信息，请参考：

解决方法 (Workarounds)

Here, we’ll describe two workarounds that can be used to generate XML files without the need for third-party components. The first can be used only with SQL Server source, while the second can be used with any data source.

在这里，我们将介绍两个解决方法，这些解决方法可用于生成XML文件，而无需第三方组件。 第一个只能与SQL Server源一起使用，而第二个可以与任何数据源一起使用。

SQL FOR XML子句 (SQL FOR XML clause)

One of the simplest solutions for generating an XML File without the need for an SSIS XML Destination is to read data as XML from the data source and write it to a flat file. In SQL Server, one of the most popular solutions to read data as XML is by using a FOR XML clause within the SELECT statement. As an example, in the AdventureWorks2017 database, we tried the following query:

无需SSIS XML Destination即可生成XML文件的最简单解决方案之一是从数据源读取XML数据并将其写入平面文件。 在SQL Server中，将数据读取为XML的最受欢迎的解决方案之一是通过在SELECT语句中使用FOR XML子句。 例如，在AdventureWorks2017数据库中，我们尝试了以下查询：

SELECT TOP (1000) [BusinessEntityID]      ,[PersonType]      ,[NameStyle]      ,[Title]      ,[FirstName]      ,[MiddleName]      ,[LastName]      ,[Suffix]      ,[EmailPromotion]      ,[AdditionalContactInfo]      ,[Demographics]      ,[rowguid]      ,[ModifiedDate]  FROM [AdventureWorks2017].[Person].[Person]  FOR XML AUTO

The result is shown in the image below:

结果如下图所示：

If you click on the XML value, you can see the whole command result written as XML.

如果单击XML值，则可以看到整个命令结果都写为XML。

To implement that in SSIS, first add an OLE DB connection manager to define a connection with the SQL Server instance. Next, add a flat file connection manager, uncheck the Columns names in first data row checkbox, then go to the Advanced Tab, add one column and change its data type to Unicode text stream DT_NTEXT and make sure that the file name specified has an .xml extension instead of .txt or .csv:

要在SSIS中实现该功能，请首先添加OLE DB连接管理器以定义与SQL Server实例的连接。 接下来，添加一个平面文件连接管理器，取消选中“ 第一个数据行中的列名称”复选框，然后转到“高级”选项卡，添加一个列并将其数据类型更改为Unicode文本流DT_NTEXT，并确保指定的文件名带有。 xml扩展名，而不是.txt或.csv：

After creating connection managers, add a data flow task where you add an OLE DB source and a flat file destination. In the OLE DB source, change the data access mode to SQL Command. Then, if you try to use the query mentioned above, you will get a set of rows containing the System.Byte[] value shown below, since there is a problem reading the XML data type from SQL Server:

创建连接管理器后，添加数据流任务，在其中添加OLE DB源和平面文件目标。 在OLE DB源中，将数据访问模式更改为SQL Command 。 然后，如果尝试使用上述查询，由于从SQL Server读取XML数据类型存在问题，因此将获得包含下面所示System.Byte []值的行集：

To fix this problem, you have to read the whole result as a single text value. You can do that by adding another SELECT clause and by assigning an alias to the whole query as follows:

要解决此问题，您必须将整个结果读取为单个文本值。 您可以通过添加另一个SELECT子句并为整个查询分配别名来做到这一点，如下所示：

SELECT (SELECT TOP (1000) [BusinessEntityID]      ,[PersonType]      ,[NameStyle]      ,[Title]      ,[FirstName]      ,[MiddleName]      ,[LastName]      ,[Suffix]      ,[EmailPromotion]      ,[AdditionalContactInfo]      ,[Demographics]      ,[rowguid]      ,[ModifiedDate]  FROM [AdventureWorks2017].[Person].[Person]  FOR XML AUTO) as Person

Now, if you click the preview button, you can see the XML value as shown below:

现在，如果单击预览按钮，则可以看到XML值，如下所示：

Now, open the flat file destination and map the input column to the column defined in the flat file connection manager.

现在，打开平面文件目标并将输入列映射到平面文件连接管理器中定义的列。

When you execute the package, you can see that only one row is transferred:

执行该程序包时，可以看到仅传输了一行：

On the other hand, when you open the XML file, you can see that the whole result is exported.

另一方面，当您打开XML文件时，可以看到整个结果都已导出。

For more information on FOR XML clause in SQL Server, refer to this article:

有关SQL Server中的FOR XML子句的更多信息，请参考本文：

脚本组件目标 (Script component destination)

The Second SSIS XML Destination workaround can be done using two steps:

可以使用两个步骤来完成第二个SSIS XML Destination解决方法：

1. Send data to a RecordSet destination
   将数据发送到RecordSet目标
2. Use a script task to convert the RecordSet into a DataTable object
   使用脚本任务将RecordSet转换为DataTable对象
3. Use the DataTable.WriteXML() function to export the XML file
   使用DataTable.WriteXML（）函数导出XML文件

Here’s a detailed example of this solution:

这是此解决方案的详细示例：

First, create a variable of type System.Object i.e., @[User::ResultTable].

首先，创建类型为System.Object的变量，即@ [User :: ResultTable] 。

Next, add an OLE DB source that reads data from the [Person].[Person] table found in the AdventureWorks2017 database. Then, add a Recordset Destination and store the result within the @[User::ResultTable] variable.

接下来，添加一个OLE DB源，该源从AdventureWorks2017数据库中的[Person]。[Person]表中读取数据。 然后，添加一个Recordset目标并将结果存储在@ [User :: ResultTable]变量中。

As shown above, the Recordset Destination converts the fill the data into an ADO Recordset that can be consumed using a script task:

如上所示，Recordset Destination将填充数据转换为可以使用脚本任务使用的ADO Recordset：

Then, outside the data flow task, add a script task and select @[User::ResultTable] as ReadOnly variable:

然后，在数据流任务之外，添加一个脚本任务，然后选择@ [User :: ResultTable]作为ReadOnly变量：

In the Script Editor, use an OledbDataAdapter class first in order to fill the ADO Recordset stored in the variable within a DataTable object. Then use the DataTable.WriteXML() function to write the DataTable into an XML file. You can use similar code:

在脚本编辑器中，首先使用OledbDataAdapter类，以填充存储在DataTable对象内的变量中的ADO Recordset。 然后使用DataTable.WriteXML（）函数将DataTable写入XML文件。 您可以使用类似的代码：

public void Main(){    DataTable dt;     dt = (new DataSet("ResultDataSet")).Tables.Add("ResultTable");     (new OleDbDataAdapter()).Fill(dt, Dts.Variables["User::ResultTable"].Value);     dt.WriteXml(@"D:\Result.xml");    Dts.TaskResult = (int)ScriptResults.Success;}

Run the package and check that the resulting XML file is created successfully.

运行程序包，并检查是否成功创建了生成的XML文件。

To learn more about DataTable and handling ADO RecordSet, refer to:

要了解有关DataTable和处理ADO RecordSet的更多信息，请参阅：

局限性 (Limitations)

One main limitation for both workarounds is they may not be able to handle a huge volume of data, since both store data in memory before exporting to the XML file.

这两种解决方法的一个主要限制是它们可能无法处理大量数据，因为这两种方法都在导出到XML文件之前将数据存储在内存中。

参考和外部链接 (References and external links)

翻译自:

ssis导入xml