Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor

本文转自：http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx

I was working on a SSIS Data Flow Task by passing Package Variables into a Stored Procedure. I will be using [AdventureWorks] sample database included with the SQL Server installation. Below is the screenshot of bits and pieces of my SSIS Package configuration.

As you can see, I have defined 2 Package Variables and using the [AdventureWorks] database connection and a Flat File Connection Manager to dump out the [uspGetWhereUsedProductID] Stored Procedure‘s output.

This is the SQL command text I am using in the OLE DB Source Editor dialog window.

EXEC [dbo].[uspGetWhereUsedProductID] ?, ?

Here is the screenshot of the Parameter Mapping.

As you can see, Parameter0 and Parameter1 were used to match the Stored Procedure‘s parameters‘ ordinal positions. When I execute the Data Flow Task, I get the following error message.

The SQL command requires a parameter named "@ParameterName", which is not found in the parameter mapping. component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0207014.

It was obvious that I am not mapping the Stored Procedure‘s parameters with the SSIS Package Variables correctly. I looked up OLE DB Source in the MSDN Library Documentation. The Specifying Parameters by Using Names section used AdventureWorks database‘s [uspGetWhereUsedProductID] stored procedure as an example and passing in @StartProductID and @CheckDate parameters.

The following SQL statement runs the uspGetWhereUsedProductID stored procedure, available in the AdventureWorks database.
EXEC uspGetWhereUsedProductID ?, ?
The stored procedure expects the variables, @StartProductID and @CheckDate, to provide parameter values. The order in which the parameters appear in the Mappings list is irrelevant. The only requirement is that the parameter names match the variable names in the stored procedure, including the @ sign.

That is *exactly* what I am doing and I am getting the error. So I am pretty sure the MSDN Documentation is not clear enough (or maybe even incorrect).
Additionally, I looked up How to: Map Query Parameters to Variables in a Data Flow Component listed under the Configuring the OLE DB Source section. Again, it doesn‘t give any more information. By this point, I was getting pretty frustrated.
I browsed the MSDN Forums and discovered that many other developers are having the SAME problem. I carefully re-read the OLE DB Source documentation in case I missed something and finally noticed this line.

The only requirement is that the parameter names match the variable names in the stored procedure, including the @ sign.

I added/replaced the following with the actual Stored Procedure‘s Parameter names.

• The Question Marks (?) in the SQL command text
• Parameter0 and Parameter1 in the Set Query Parameters dialog window

I was very happy to discover that my SSIS Package runs successfully after the changes.
I really wished the MSDN Documentation team gave more details on OLE DB Source Parameter Mapping. It would have saved me the headache of looking for a solution and feeling like pulling out my hair.
Happy Programming, Soe

[转]Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor,,5-wow.com