如何将值传递给OLE DB Source组件中的存储过程参数? [英] How do I pass value to a stored procedure parameter in OLE DB Source component?

问题描述

我正在使用SSIS2008.我有一个选择查询名称​​ sqlquery1 ，该查询返回一些行:

aq
dr
tb

此查询目前尚未在SSIS上实现.

我正在从数据流任务中的OLE DB源调用存储过程.我想将从查询中获得的数据传递给存储过程参数.

示例:

我想通过传递第一个值 aq

来调用存储过程

storedProdecure1 'aq'

然后传递第二个值 dr

storedProdecure1 'dr'

我想那将是一个循环.我需要这样做是因为 OLE DB Source 通过存储过程生成的数据需要发送到另一个目标，并且必须对 sqlquery1 的每条记录执行此操作. /p>

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

在SSIS中我该怎么做?

解决方案

从概念上讲，您的解决方案将是执行源查询以生成结果集.将其存储到变量中，然后需要对这些结果进行迭代，并且对于每一行，都需要使用该行的值调用存储过程，然后将结果发送到新的Excel文件中.

我希望您的包裹看起来像这样

执行SQL任务，名为"SQL Load Recordset"，附加到名为"FELC Shred Recordset"的Foreach循环容器.嵌套在其中，我有一个名为"FST复制模板"的文件系统任务，它是名为"DFT生成输出"的数据流任务的优先级.

设置

作为您的初学者，我将尝试详细解释.为了避免麻烦，请获取 BIDSHelper 的副本.这是一个免费的开源工具，可改善BIDS/SSDT的设计体验.

变量

单击控制流"的背景.没有选择任何内容，右键单击并选择变量.在弹出的新窗口中，单击创建新变量的按钮4次.单击任何内容的原因是在SQL Server 2012之前，变量创建的默认行为是在当前对象的范围内创建变量.这给新手和有经验的开发人员都造成了很多损失.变量名称区分大小写，因此请注意.

1. 将变量重命名为RecordSet.将数据类型从Int32更改为Object
2. 将Variable1重命名为ParameterValue.将数据类型从Int32更改为String
3. 将Variable2重命名为TemplateFile.将数据类型从Int32更改为String.将值设置为输出Excel文件的路径.我使用了C:\ ssisdata \ ShredRecordset.xlsx
4. 将变量4重命名为OutputFileName.将数据类型从Int32更改为String.在这里，我们将做一些稍微高级的事情.单击变量，然后按F4弹出属性"窗口.将EvaluateAsExpression的值更改为True.在表达式"中，将其设置为"C:\\ssisdata\\ShredRecordset." + @[User::ParameterValue] + ".xlsx"(或任何文件和路径).这样做是将变量配置为随着ParameterValue的值更改而更改.这有助于确保我们获得唯一的文件名.欢迎您根据需要更改命名约定.请注意，在表达式中的任何时间都需要转义\.

连接管理器

我已经假设您正在使用OLE DB连接管理器.我的被​​命名为FOO.如果您使用的是ADO.NET，则概念将相似，但在参数等方面会有细微差别.

您还需要第二个连接管理器来处理Excel.如果SSIS在数据类型方面气质十足，则Excel在使您沉睡于数据类型时会精神错乱地刺入您的后叉.我们将等待，让数据流实际创建此连接管理器，以确保我们的类型正确.

对结果集的源查询

SQL Load Recordset是执行SQL任务的实例. .在这里，我有一个简单的查询来模仿您的来源.

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

在常规"选项卡上需要注意的重要一点是，我已将ResultSet从None切换为Full result set.这样做会使结果集"选项卡从变灰变为可用.

您可以观察到我已经为上面创建的变量(User :: RecordSet)分配了变量名，并且我的结果名是0.这很重要，因为默认值NewResultName不起作用.

FELC切细记录集

获取一个 Foreach循环容器，我们将其用于切碎"在上一步中生成的结果.

将枚举器配置为Foreach ADO Enumerator.将User::RecordSet用作ADO对象源变量.选择rows in the first table作为枚举模式

在变量映射"选项卡上，您将需要选择变量User::ParameterValue并为其指定索引0.这将导致记录集对象中的zerotth元素被分配给变量ParameterValue.务必要有数据类型协议，因为SSIS不会在此处进行隐式转换.

FST复制模板

这是文件系统任务.我们将复制模板Excel文件，以便我们有一个命名良好的输出文件(其中包含参数名称).将其配置为

• IsDestinationPathVariable:正确
• DestinationVarible:User :: OutputFileName
• OverwriteDestination:正确
• 操作:复制文件
• IsSourcePathVariable:正确
• SourceVariable:User :: TemplateFile

DFT生成输出

这是数据流任务.我假设您只是将结果直接转储到文件中，所以我们只需要一个 Excel目标

OLEDB dbo_storedProcedure1

这是使用控制流中切细的参数从源系统中提取数据的地方.我将在此处编写查询，并使用?表示它具有参数.

将您的数据访问模式更改为"SQL命令"，并在可用的SQL命令文本中输入您的查询

EXECUTE dbo.storedProcedure1 ?

我单击参数..."按钮，然后将其填写，如图所示

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

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

我们现在需要填充Excel工作表的名称.单击该新建..."按钮，这可能会提示您没有足够的有关映射数据类型的信息.别担心，这是半标准.然后它将弹出一个表定义，例如

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

表"名称将是工作表名称，或者确切地说是工作表中的命名数据集.我制作了Sheet1，然后单击确定".现在工作表已存在，请在下拉菜单中选择它.我将Sheet1 $作为目标工作表名称.不确定是否会有所不同.

单击映射"选项卡，事物应该会自动映射，所以请单击确定".

最后

在这一点上，如果我们运行软件包，它将每次覆盖模板文件.秘密是我们需要告诉我们Excel Connection Manager我们只是使它不必具有硬编码名称.

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

如果您的图标和此类图标看起来有所不同，那是可以预期的.这是使用SSIS 2012记录的.只是外观不同，您的工作流程在2005和2008/2008R2中将是相同的.

如果运行此程序包甚至无法启动，并且ACE 12或Jet 4.0出现错误，则说明某些程序不可用，那么您使用的是64位计算机，则需要告知BIDS/SSDT您要运行在32位模式下.

确保Run64BitRuntime值为False.可以通过右键单击该项目，然后展开配置属性"来找到该项目设置，它将成为调试"下的一个选项.

进一步阅读

可以在 解决方案

Conceptually, what your solution will look like is an execute your source query to generate your result set. Store that into a variable and then you'll need to do iterate through those results and for each row, you'll want to call your stored procedure with that row's value and send the results into a new Excel file.

I'd envision your package looking something like this

An Execute SQL Task, named "SQL Load Recordset", attached to a Foreach Loop Container, named "FELC Shred Recordset". Nested inside there I have a File System Task, named "FST Copy Template" which is a precedence for a Data Flow Task, named "DFT Generate Output".

Set up

As you're a beginner, I'm going to try and explain in detail. To save yourself some hassle, grab a copy of BIDSHelper. It's a free, open source tool that improves the design experience in BIDS/SSDT.

Variables

Click on the background of your Control Flow. With nothing selected, right-click and select Variables. In the new window that pops up, click the button that creates a New Variable 4 times. The reason for clicking on nothing is that until SQL Server 2012, the default behaviour of variable creation is to create them at the scope of the current object. This has resulted in many lost hairs for new and experienced developers alike. Variable names are case sensitive so be aware of that as well.

1. Rename Variable to RecordSet. Change the Data type from Int32 to Object
2. Rename Variable1 to ParameterValue. Change the data type from Int32 to String
3. Rename Variable2 to TemplateFile. Change the data type from Int32 to String. Set the value to the path of your output Excel File. I used C:\ssisdata\ShredRecordset.xlsx
4. Rename Variable 4 to OutputFileName. Change the data type from Int32 to String. Here we're going to do something slightly advanced. Click on the variable and hit F4 to bring up the Properties window. Change the value of EvaluateAsExpression to True. In Expression, set it to "C:\\ssisdata\\ShredRecordset." + @[User::ParameterValue] + ".xlsx" (or whatever your file and path are). What this does, is configures a variable to change as the value of ParameterValue changes. This helps ensure we get a unique file name. You're welcome to change naming convention as needed. Note that you need to escape the \ any time you are in an expression.

Connection Managers

I have made the assumption you are using an OLE DB connection manager. Mine is named FOO. If you are using ADO.NET the concepts will be similar but there will be nuances pertaining to parameters and such.

You will also need a second Connection Manager to handle Excel. If SSIS is temperamental about data types, Excel is flat out psychotic-stab-you-in-the-back-with-a-fork-while-you're-sleeping about data types. We're going to wait and let the data flow actually create this Connection Manager to ensure our types are good.

Source Query to Result Set

The SQL Load Recordset is an instance of the Execute SQL Task. Here I have a simple query to mimic your source.

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

What's important to note on the General tab is that I have switched my ResultSet from None to Full result set. Doing this makes the Result Set tab go from being greyed out to usable.

You can observe that I have assigned the Variable Name to the variable we created above (User::RecordSet) and I the Result Name is 0. That is important as the default value, NewResultName doesn't work.

FELC Shred Recordset

Grab a Foreach Loop Container and we will use that to "shred" the results that were generated in the preceding step.

Configure the enumerator as a Foreach ADO Enumerator Use User::RecordSet as your ADO object source variable. Select rows in the first table as your Enumeration mode

On the Variable Mappings tab, you will need to select your variable User::ParameterValue and assign it the Index of 0. This will result in the zerotth element in your recordset object being assigned to the variable ParameterValue. It is important that you have data type agreement as SSIS won't do implicit conversions here.

FST Copy Template

This a File System Task. We are going to copy our template Excel File so that we have a well named output file (has the parameter name in it). Configure it as

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

DFT Generate Output

This is a Data Flow Task. I'm assuming you're just dumping results straight to a file so we'll just need an OLE DB Source and an Excel Destination

OLEDB dbo_storedProcedure1

This is where your data is pulled from your source system with the parameter we shredded in the Control Flow. I am going to write my query in here and use the ? to indicate it has a parameter.

Change your Data access mode to "SQL Command" and in the SQL command text that is available, put your query

EXECUTE dbo.storedProcedure1 ?

I click the Parameters... button and fill it out as shown

• Parameters: @parameterValue
• Variables: User::ParameterValue
• Param direction: Input

Connect an Excel Destination to the OLE DB Source. Double click and in the Excel Connection Manager section, click New... Determine if you're needing 2003 or 2007 format (.xls vs .xlsx) and whether you want your file to have header rows. For you File Path, put in the same value you used for your @User::TemplatePath variable and click OK.

We now need to populate the name of the Excel Sheet. Click that New... button and it may bark that there is not sufficient information about mapping data types. Don't worry, that's semi-standard. It will then pop up a table definition something like

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

The "table" name is going to be the worksheet name, or precisely, the named data set in the worksheet. I made mine Sheet1 and clicked OK. Now that the sheet exists, select it in the drop down. I went with the Sheet1$ as the target sheet name. Not sure if it makes a difference.

Click the Mappings tab and things should auto-map just fine so click OK.

Finally

At this point, if we ran the package it would overwrite the template file every time. The secret is we need to tell that Excel Connection Manager we just made that it needs to not have a hard coded name.

Click once on the Excel Connection Manager in the Connection Managers tab. In the Properties window, find the Expressions section and click the ellipses ... Here we will configure the Property ExcelFilePath and the Expression we will use is @[User::OutputFileName]

If your icons and such look different, that's to be expected. This was documented using SSIS 2012. Your work flow will be the same in 2005 and 2008/2008R2 just the skin is different.

If you run this package and it doesn't even start and there is an error about the ACE 12 or Jet 4.0 something not available, then you are on a 64bit machine and need to tell BIDS/SSDT that you want to run in 32 bit mode.

Ensure the Run64BitRuntime value is False. This project setting can be found by right clicking on the project, expand the Configuration Properties and it will be an option under Debugging.

Further reading

A different example of shredding a recordset object can be found on How to automate the execution of a stored procedure with an SSIS package?

这篇关于如何将值传递给OLE DB Source组件中的存储过程参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！