Unable to pass a variable value to a stored procedure in ssis

Question

When executing an SSIS package, the following error appears:

[OLE DB Source [83]] Error: The SQL command requires a parameter named "@Sales_person", which is not found in the parameter mapping.

[SSIS.Pipeline] Error: OLE DB Source failed the pre-execute phase and returned error code 0xC0207014.

Below is the screenshot of my OLE DB Source editor

I do see Param direction tab in Set Query parameters, how is that used? In my case will I be using Input or Output or InputOutput

Answer 1

After searching i didn't find a solution for this issue that worked for me. Ther are many suggestions like adding SET NOCOUNT ON before the execute command. Below some related links:

• http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx
• http://www.sqlservercentral.com/articles/Data+Flow+Task+(SSIS)/117370/
• http://www.ssistalk.com/2007/10/10/ssis-stored-procedures-and-the-ole-db-source/

You can do a workaround

Declare a SSIS variable (assuming @[User::Query])

Set @[User::Query] property EvaluateAsExpression = True and use the following expression

"EXEC [dbo].[GetDales_Person_data] " + @[User::Sales]

if @[User::Sales] is a string use the following

"EXEC [dbo].[GetDales_Person_data] '" + @[User::Sales] + "'"

Then In OLEDB Source use SQL Command from variable and select @[User::Query]