Clever Geek Handbook

Cannot pass value to variable to stored procedure in ssis

When executing an SSIS package, the following message appears:

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

[SSIS.Pipeline] Error: The OLE DB source did not pass the pre-execution phase and returned error code 0xC0207014.

Below is a screenshot of my original OLE DB editor enter image description here

enter image description here

enter image description here

I see the Param direction tab in the Set Query parameters, how is this used? In my case, I will use Input

either Output

orInputOutput

+5


source to share


3 answers


After searching, I didn't find a solution for this problem that worked for me. There are many suggestions like adding SET NOCOUNT ON

before the execute command. Below are some links:

You can do a workaround

Declare 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 OLEDB Source

use In SQL Command from variable

and select@[User::Query]

+2


source


The problem is display. See image and source below, to make the fix, you have to install it:

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


enter image description here

Source - Blogging Geek

0


source


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

One way to get around this is to use:

DECLARE @SalesPerson int = ? -- or whatever datatype

EXEC [dbo].[GetSales_Person_Data] @SalesPerson
0


source






More articles:


All Articles