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

问题描述

我正在使用 SSIS 2008.我有一个选择查询名称 sqlquery1 返回一些行:

aq博士结核病

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

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

示例:

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

storedProdecure1 'aq'

然后传递第二个值dr

storedProdecure1 'dr'

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

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

我需要如何在 SSIS 中执行此操作?

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

我想你的包裹看起来像这样

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

设置

由于您是初学者，我将尝试详细解释.为避免麻烦，请获取一份 ，我们将使用它来粉碎"上一步中生成的结果.

将枚举器配置为 Foreach ADO 枚举器 使用 User::RecordSet 作为您的 ADO 对象源变量.选择第一个表中的行作为您的枚举模式

在变量映射选项卡上，您需要选择变量 User::ParameterValue 并为其分配索引 0.这将导致记录集对象中的第零个元素被分配给变量参数值.重要的是您有数据类型协议，因为 SSIS 不会在这里进行隐式转换.

FST 复制模板

这是一个.我假设您只是将结果直接转储到文件中，因此我们只需要一个

I am working with SSIS 2008. I have a select query name sqlquery1 that returns some rows:

aq
dr
tb

This query is not implemented on the SSIS at the moment.

I am calling a stored procedure from an OLE DB Source within a Data Flow Task. I would like to pass the data obtained from the query to the stored procedure parameter.

Example:

I would like to call the stored procedure by passing the first value aq

storedProdecure1 'aq'

then pass the second value dr

storedProdecure1 'dr'

I guess it would be something like a cycle. I need this because the data generated by the OLE DB Source through the stored procedure needs to be sent to another destination and this must be done for each record of the sqlquery1.

I would like to know how to call the query sqlquery1 and pass its output to call another stored procedure.

How do I need to do this in SSIS?

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:ssisdataShredRecordset.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 源组件中的存储过程参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！