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

Shostakovich

Error: OLE DB Source failed the pre-execute phase and returned error code 0xC0207014. It sounds like if you apply delay validation to the properties of the data flow task that may solve your issue. You can also make the expression User::SALES constantly evaluate itself so it appears present by the flow, this should allow you to get past the pre-execute phase when it's doing a validation check. – Untitled

DelayValidation is True in the Data Flow task – Shostakovich

@Shostakovich take a look at my answer and give me a reply if it is not working – Klingel

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]

Klingel

sqlservercentral.com/articles/Data+Flow+Task+(SSIS)/117370 helped me to overcome the issue. – Shostakovich

Answer 2

The problem is with the mapping. See image and source below to make corrections, you should set it to:

Exec [dbo].[GetSales_Person_Data] @Sales_person = ?

Source - geek with blogs

Untitled

it throws an error 'The EXEC SQL construct or statement is not supported.' – Shostakovich

What if you use [dbo].[GetSales_Person_Data] @Sales_person = ? . You may have to leave out the exec. Source – Untitled

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

What if you set your expression User::Sales to True under EvaluateAsExpression? Source – Untitled

Please attribute your sources: It appears that you just copied straight from here: geekswithblogs.net/stun/archive/2009/03/05/… – Jonette

@Nick.McDermaid the source is already attributed to it below the screen shot. . – Untitled

Oh sorry about that. – Jonette

Answer 3

There seems to be a problem with mapping parameters directly into the EXEC statement.

One way you can get around this is to use:

DECLARE @SalesPerson int = ? -- or whatever datatype

EXEC [dbo].[GetSales_Person_Data] @SalesPerson

Extensor