Stun Press

My attempt at solving tech problems my way…

Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor

Posted by Soe Tun on March 3, 2009

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.

My SSIS Package Configuration Overview

My SSIS Package Configuration Overview

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.

Incorrect Stored Procedure Parameter Mapping

Wrong Way to Map Stored Procedure's Parameters in OLE DB Source Editor

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
Correct Way to Map Stored Procedures Parameters in OLE DB Source

Correct Way to Map Stored Procedure's Parameters in OLE DB Source Editor

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

About these ads

2 Responses to “Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor”

  1. HP said

    Hi Soe,
    Thanks for the above details.

    When I tried to preview or view columns it is giving me
    “Syntax error,permission violation, or other nonspecific error”

    Can you please help me on this?

    Thanks

  2. Soe Tun said

    Hi HP,

    (1) are you *dynamically* building SQL code inside your Stored Procedure?
    your dynamically concatenated SQL string might have syntax error

    (2) can you run the Stored Procedure with the SAME input values and get a result set?
    please make sure you can run the stored procedure with the same parameter values from SQL Management Studio (SSMS)

    (3) please check your Database Connection string as well. Make sure you are using the same when you are running the SSIS and when running inside SSMS (Mgmt Studio).
    Are you using any System tables or views?
    Make sure the SQL Authentication Account you are using has enough privileges

    Please post some more information like exact error message code and etc if you need more help.
    I can only give you a generalized troubleshooting tips.

    - Soe

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: