Execute SQL Task - Parameter Mapping For Stored Procedures

Hi ALL,

I have a Execute SQL Task to execute a stored procedure. It has no input and output parameters.

The stored procedure is defined as follows:


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SetSLATimePriority]

AS

BEGIN

DECLARE @PriorityID tinyint,@MAXPriorityID tinyint

DECLARE @Priority NVARCHAR(MAX), @SLATime int

SET @PriorityID=1

SET @MAXPriorityID=0

SELECT @MAXPriorityID=MAX([Priority ID]) FROM [MTD Dashboard].[dbo].[Priority]

SET NOCOUNT ON;

WHILE @PriorityID<=@MAXPriorityID

BEGIN

SELECT @Priority= [Priority] FROM [MTD Dashboard].[dbo].[Priority]

WHERE [Priority ID]=@PriorityID

SELECT @SLATime= [SLA Time in hours] FROM [MTD Dashboard].[dbo].[Priority]

WHERE [Priority ID]=@PriorityID

UPDATE [MTD Dashboard].[dbo].[Remedy Dump-Filtered]

SET [SLA Time] = @SLATime WHERE [Priority] like @Priority

SET @PriorityID=@PriorityID+1

END

END


The Properties of Execute SQL Task are set as follows:

Result Set: None
Connection Type: OLEDB
SQL Source Type: Direct Input
SQL Statement: EXEC ? = [dbo].[SetSLATimePriority]
IsQueryStoredProcedure: True
ByPassPrepare: False

Parameter Mapping:

Variable Name : User::IntValue
Direction: ReturnValue
Data Type: Long
ParameterName: 0

I am getting the following error, when I run this package.

[Execute SQL Task] Error: Executing the query "EXEC ? = [dbo].[SetSLATimePriority]" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am not able to figure out, where exactly the problem is.. Can some one please help me out?