Mapping Stored Procedures in SSIS OLE DB Source Editor

Case

Usually I use the "Table of view" or a plain SQL command in an OLE DB Source Editor to collect the data I need. That is rather straight forward and simple. Last week I needed to add some more steps and logic in my query and decided to go for a Stored Procedure. To make it more usable, I wanted to add a parameter. Peanuts one would think. Everything seemed to work fine on design mode, I even got a list of expected columns when clicking on the columns tab in the OLE DB Source Editor.

Well... It didn't work.

For this tutorial I used the AdvancedWorks database voor SQL Server 2008

I started with creating a simple SPROC which returns all products based on size and color. You can ofcourse replace this with your own SPROC. After that I defined my variables and created a connection to the database. I also created a connection to a flat file just to send the data somewhere and check if it is correct.

After that I added a Data Flow Task in my project and an OLE DB Source in there. Linked it to AdventureWorks and chose for SQL Command as Data Access Mode. See the following screenshot.

This seems all right. I even got the column list back when clicking on "Columns".

Unfortunatelly when running the package the following error occurs:

[Get Products By Color And Size [1]] Error: The SQL command requires a parameter named "@Size", which is not found in the parameter mapping.

What happened? I did pass the parameters so what's the problem??

Solution

Well, it seems like SSIS really wants the parameters linked to the SPROC having exactly the same name as the parameters in your SPROC.

Check the changes in the "SQL Command" and the "Set Query Parameters" windows in the following screenshot.

You can download a copy of the package with the link below:
MappingSPROCParamsInSSIS.dtsx