每个单行输入从脚本组件输出多行 [英] Output multiple rows from script component per single row input

问题描述

我对 SSIS 缺乏经验，但我在 SQL 和 C# 以及其他技术方面有很多经验.

I'm pretty inexperienced with SSIS, though I have much experience in SQL and C# and other technologies.

我正在将我作为独立 c# 控制台应用程序编写的任务转换为 SSIS 包.

I am converting a task I have written as a stand-alone c# console app into an SSIS package.

我有一个 OLEDB 输入源，它是一个 SQL 命令，它收集数据库中的某些数据，然后我将这些数据输入到脚本组件转换中.我使用输入字段作为基于 OAuth 的 Restful Web 服务的参数，这需要大量自定义 C# 代码才能完成.Web 服务返回一个 XML 响应，其中包含必须为每个输入行输出的许多行.

I have a OLEDB input source that is a SQL command, this collects certain data in the database that I then feed into a Script Component Transform. I use the input fields as parameters to an OAuth based restful web service, which requires a lot of custom C# code to accomplish. The web service returns an XML respose that includes many rows that must be output for each input row.

我对脚本转换如何工作的理解是，它或多或少是一行进，一行出.

My understanding of how the script transform works is that it's more or less one row in, one row out.

我真的有几个问题.

1. 以这种方式使用输入源是个好主意吗?或者是否有更好的方法将输入行输入到我的网络服务中?
2. 脚本组件是否是此处使用的正确工具?我无法使用普通的 Web 服务，因为该 Web 服务不是基于 SOAP 或 WCF 的，并且在请求中需要 OAuth.(或者有没有办法以这种方式使用 Web 服务组件?)
3. 如何为每个输入行输出多于一行?
4. SSIS 是否支持获取 XML 结果(包含多行)并将它们映射到脚本转换中输出字段的行的方法?我知道有一个 XML Input 源，但这不是真的.我在想一些接受 XML 输入并吐出数据行的东西

更新:

来自 Web 服务的数据如下所示(省略了多余的内容):

Data from the Web Service looks like this (extra cruft elided):

<user>
  <item>
    <col1>1</col1>
    <col2>2</col2>
    <col3>3</col3>
  </item>
  <item>
    <col1>1</col1>
    <col2>2</col2>
    <col3>3</col3>
  </item>
  ....
</user>

本质上，SQL 数据源返回用户数据集.用户数据集被输入脚本并用作 Web 服务调用的参数.Web 服务调用返回一组 XML 结果，其中包含必须从脚本输出的多行"数据.

Essentially, the SQL DataSource returns a dataset of of users. The users dataset is fed into the script and used as parameters for the web service calls. The web service calls return a set of XML results, which have multiple "rows" of data that must be output from the script.

在上述数据中，对于输入源中提供的每个用户，脚本的输出将是多行 col1、col2 和 col3.我需要一种方法来提取这些元素并将它们放入输出缓冲区中每一行 xml 数据的列中.或者，一种简单地使 xml 成为脚本输出并将该输出提供给另一个组件以将 xml 解析为行的方法(就像 XML 源一样，但显然您不能将 XML 源放在数据中间流).

In the above data, the outputs of the script would be multiple rows of col1, col2, and col3 for each user supplied in the input source. I need a way to extract those elements and put them into columns in the output buffer for each row of xml data. Or, a way to simply make the xml the output of the script and feed that output into another component to parse the xml into rows (like an XML source does, but obviously you can't put an XML source in the middle of a data flow).

推荐答案

尽我所能

这取决于但通常情况下，如果您的数据位于数据库中，OLE DB 或 ADO.NET 源是您将其注入管道的首选组件.更好的?这取决于您的需求，但您是否有理由认为它不是可取的?使用数据流的好处是建立在缓冲、并行性、日志记录、配置等方面.我假设或其他一些原因导致您将 .NET 应用程序移动到集成服务包中，所以我想如果您正在进入这个空间，全力以赴.

It depends but generally, if your data is in a database, an OLE DB, or ADO.NET source is your preferred component for injecting it into the pipeline. Better? It depends on your needs but is there a reason you think it wouldn't be advisable? Nice benefits to using a data flow are built in buffering, parallelism, logging, configuration, etc. I'm assuming that or some other reason is leading you to move your .NET app into an Integration Services package so I would think if you're moving into this space, go whole hog.

肯定的.内置的 Web 服务内容不及工业强度.您已经熟悉 .NET，因此您可以充分利用该组件.

Definitely. The built-in web-service stuff is less-than-industrial-strength. You're already familiar with .NET so you're well positioned to take maximum advantage of that component.

是的.您对 1:1 输入:输出的假设仅适用于默认行为.默认情况下，脚本组件是同步的，因此正如您所观察到的，每一行都有一个输出.但是，通过将您的脚本组件更改为 异步 组件，那么您可以将 1B 行转换为单行输出或让 1 行源生成 N 行输出.对于物料清单类型的问题，我不得不执行后者——我会收到一个父 ID，我必须查找与父相关联的所有子行.无论如何，链接的 MSDN 文章描述了如何使其异步.

Yes. Your assumption of 1:1 input:output is only for the default behaviour. By default, a script component is synchronous so as you've observed, every row has an output. But, by changing your script component to becoming an asynchronous component, then you can have 1B rows transformed into a single row of output or have 1 row of source generate N rows of output. I had to do the latter for a Bill of Materials type problem---I'd receive a parent id and I'd have to lookup all the child rows associated to the parent. Anyways, the linked MSDN article describes how to make it async.

我不太明白您要解决这个问题的内容.为这个假人设计一些例子，我会看看它是否会点击.

I don't understand well enough what you're asking to address this. Dummy up some examples for this dummy and I'll see if it clicks.

这篇关于每个单行输入从脚本组件输出多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！