如何在OLE DB源组件中传递值给存储过程参数？

Question

我正在使用 SSIS 2008。我有一个名为 sqlquery1 的查询语句，它返回一些行：

aq
dr
tb

目前，SSIS 上尚未实现此查询。

我正在从数据流任务中的 OLE DB 源调用一个存储过程。我希望将从查询获取的数据传递给存储过程参数。

示例：

我想通过传递第一个值 aq 来调用存储过程。

storedProdecure1 'aq'

然后传递第二个值dr

storedProdecure1 'dr'

我猜这会是一个循环。我需要做这个的原因是通过存储过程由OLE DB Source生成的数据需要被发送到另一个目的地，并且必须对sqlquery1的每条记录执行此操作。

我想知道如何调用查询sqlquery1并将其输出传递给另一个存储过程。

在SSIS中，我需要怎样做？

- pyram

---

您有一个查询返回了上述数据集。我不确定它如何与您想在OLE DB源中使用的存储过程相关。您能否更新您的问题？即使只是伪代码，我认为这会帮助我们理解您的问题。 - billinkc

我刚刚编辑了问题。但基本点是我希望存储过程从数据集(aq)的第一行取值，然后取第二个(dr)，以此类推。 - pyram

预期的行为是数据流对原始查询中的每个值都触发N次，还是枚举应该在OLE DB Source上进行。这取决于您的目标。如果它调用数据流N次，则需要确保平面文件目标附加而不是覆盖。 - billinkc

太好了，这是更容易回答的版本。 ;) - billinkc

数据流针对原始查询中的每个值触发N次，但每次触发时它都应该写入一个新的Excel文件。 - pyram

Answer 1

你的解决方案从概念上看，会执行源查询以生成结果集，并将其存储到一个变量中。然后，你需要遍历这些结果，对于每一行，你都需要使用该行的值调用存储过程，并将结果发送到一个新的Excel文件中。

我设想你的程序包类似于以下内容：

一个名为“SQL Load Recordset”的Execute SQL任务连接到一个名为“FELC Shred Recordset”的Foreach Loop容器，并在其中嵌套了一个名为“FST Copy Template”的File System任务，它是名为“DFT Generate Output”的Data Flow任务的前置任务。

设置

由于您是初学者，我将尽力详细解释。为了节省麻烦，请获取BIDSHelper的副本。这是一个免费的开源工具，可以改善BIDS/SSDT中的设计体验。

变量

单击控制流的背景。未选择任何内容时，请右键单击并选择“变量”。在弹出的新窗口中，点击创建四个新变量的按钮。之所以要先不选任何东西，是因为直到SQL Server 2012，变量创建的默认行为是在当前对象的范围内创建它们。这已经导致许多新手和有经验的开发人员失去了头发。变量名称区分大小写，所以请注意。

1. 将变量重命名为 RecordSet。将数据类型从 Int32 更改为 Object。 2. 将 Variable1 重命名为 ParameterValue。将数据类型从 Int32 更改为 String。 3. 将 Variable2 重命名为 TemplateFile。将数据类型从 Int32 更改为 String。将值设置为输出 Excel 文件的路径。我使用了 C:\ssisdata\ShredRecordset.xlsx。 4. 将 Variable 4 重命名为 OutputFileName。将数据类型从 Int32 更改为 String。在这里，我们要做一些稍微高级的事情。单击变量并按 F4 键以打开属性窗口。将 EvaluateAsExpression 的值更改为 True。在 Expression 中，将其设置为 "C:\\ssisdata\\ShredRecordset." + @[User::ParameterValue] + ".xlsx"（或者您的文件和路径）。这样做的作用是配置一个变量，使其随着 ParameterValue 的值的变化而变化。这有助于确保我们获得唯一的文件名。您可以根据需要更改命名约定。请注意，每当您处于表达式状态时，都需要转义 \。

连接管理器

我假设您正在使用OLE DB连接管理器，我的命名为FOO。如果您使用ADO.NET，则概念将类似，但会涉及到参数等细微差别。

您还需要第二个连接管理器来处理Excel。如果SSIS对数据类型挑剔，那么Excel就是完全疯狂的-在您睡觉时用叉子刺你的背部-关于数据类型。我们将等待数据流实际创建此连接管理器，以确保我们的类型正确无误。

源查询到结果集

SQL加载记录集是Execute SQL Task的一个实例。这里我有一个简单的查询来模拟您的源。

SELECT 'aq' AS parameterValue
UNION ALL SELECT 'dr'
UNION ALL SELECT 'tb'

需要注意的是在“常规”选项卡上，我已将ResultSet从None切换为完整结果集。这样做可以使“结果集”选项卡从灰色变为可用状态。

您可以看到我已经将变量名称分配给了我们上面创建的变量（User::RecordSet），而结果名称为0。这很重要，因为默认值NewResultName不能使用。

FELC Shred Recordset

获取一个Foreach Loop Container，我们将使用它来“分解”在前一步骤中生成的结果。

将枚举配置为Foreach ADO Enumerator，并将User::RecordSet作为ADO对象源变量。选择第一张表中的行作为枚举模式。

在“变量映射”选项卡中，您需要选择变量User::ParameterValue并将其分配给索引0。这将导致记录集对象中的第零个元素被分配给变量ParameterValue。重要的是要确保数据类型一致，因为SSIS不会在此处进行隐式转换。

FST复制模板

这是一个文件系统任务。我们将复制我们的模板Excel文件，以便我们有一个命名良好的输出文件（其中包含参数名称）。配置如下：

• IsDestinationPathVariable: True
• DestinationVarible: User::OutputFileName
• OverwriteDestination: True
• Operation: Copy File
• IsSourcePathVariable: True
• SourceVariable: User::TemplateFile

DFT生成输出

这是一个数据流任务。我假设您只是将结果直接转储到文件中，因此我们只需要一个OLE DB源和一个Excel目标。

OLEDB dbo_storedProcedure1

这是从源系统中获取数据的地方，我们在控制流中使用参数。我将在这里编写我的查询，并使用?表示它有一个参数。

将数据访问模式更改为“SQL命令”，并在可用的SQL命令文本中放置您的查询。

EXECUTE dbo.storedProcedure1 ?

我点击“参数”按钮，并按照所示填写：

• 参数：@parameterValue
• 变量：User::ParameterValue
• 参数方向：输入

将Excel目标连接到OLE DB源。双击并在Excel连接管理器部分中，单击“新建...”确定您需要2003还是2007格式（.xls vs .xlsx），以及您是否希望文件具有标题行。对于文件路径，请输入与@User :: TemplatePath变量使用的相同值，然后单击“确定”。

我们现在需要填写Excel表格的名称。点击那个“新建...”按钮，它可能会提示映射数据类型的信息不足。别担心，这是半标准的。然后它会弹出一个类似于表格定义的东西。

CREATE TABLE `Excel Destination` (
    `name` NVARCHAR(35),
    `number` INT,
    `type` NVARCHAR(3),
    `low` INT,
    `high` INT,
    `status` INT
)

"table" 名称将成为工作表名称，或者更准确地说，工作表中的命名数据集。我选择了 Sheet1 并点击了“确定”。现在该工作表已经存在，请在下拉菜单中选择它。我选择了 Sheet1$ 作为目标工作表名称。不确定是否有影响。

点击Mappings选项卡，然后事情应该会自动映射，所以点击OK。

最后

此时，如果我们运行包，它将每次覆盖模板文件。秘密在于，我们需要告诉刚刚创建的Excel连接管理器不要有硬编码的名称。

在Connection Managers选项卡上单击一次Excel连接管理器。在属性窗口中，找到Expressions部分，然后单击省略号...。在这里，我们将配置ExcelFilePath属性和我们将使用的表达式为@[User::OutputFileName]。

如果您的图标等外观不同，那是可以预料的。这是使用SSIS 2012记录的。您在2005年和2008/2008R2中的工作流程将是相同的，只是皮肤不同。

如果您运行此包并且它甚至无法启动，并且有关ACE 12或Jet 4.0的错误信息不可用，则表示您正在64位机器上，并且需要告诉BIDS / SSDT您要以32位模式运行。

确保 Run64BitRuntime 值为 False。可以通过右键单击项目，展开“配置属性”，在“调试”选项下找到此项目设置。

更多阅读

另一个撕裂记录集对象的示例可以在如何使用SSIS包自动执行存储过程？中找到。

- billinkc

---

如果 Foreach 循环中的内容没有变成绿色，则表示数据集中没有数据（或者你没有正确地解析它）。请确保将枚举器类型从默认的文件枚举器更改为 Foreach ADO 枚举器类型之一。 - billinkc

我在这里迷失了方向。这是在Foreach循环属性上吗？ - pyram

是的，请看“FELC Shred Recordset”。你的枚举器看起来像那样吗？ - billinkc

ParameterValue 被分配到 Foreach 循环中。 - billinkc

太棒了，@billinkc的回答非常好，特别是这句话：如果说SSIS对于数据类型有些难搞的话，那么Excel在处理数据类型方面就完全疯狂了——它会在你睡觉时用叉子捅你的背。 - Edmund Schweppe