SSIS Error: [Execute SQL Task] Error: Executing the query "exec ?=Authors_insert ?" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctl

Question

Hi All.

When i try to execute the below query in SQL Execute Task:

exec ?=Authors_insert ? , from here i call the stored procedure named Authors_insert as given below:

alter procedure Authors_Insert
(
@FirstName Varchar(20)
)
as
insert into Authors (FirstName) values (@FirstName)

Please find below the screenshots of my SQL Execute Task:

I am getting the below mentioned error message:

SSIS Error: [Execute SQL Task] Error: Executing the query "exec ?=Authors_insert ?" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly.

---

Ramasubramanian S

Answer 1

The string needs to be surrounded with ' s in T-SQL looks like why this insert is failing

Furthermore, you probably do not expect anything returned, so exec ? = ... is a moot point

---

Arthur My Blog

Answer 2

no screenshot attached??

---

Please mark the post as answered if it answers your question

Answer 3

In the Execute SQL task use the below query.

EXEC Authors_Insert ?

In the parameter mapping map SSIS variable you have created for FirstName with parametername as 0

Regards,

YB

Answer 4

Hi,

Thanks for your post, i could resolve this issue when i changed the Query as

EXEC Authors_Insert ?, but what is the main difference between this one and EXEC ?=Authors_Insert ?.

---

Ramasubramanian S

Answer 5

Syntax wise EXEC ?=Authors_Insert ? is  wrong.

If you execute the same in sql server management studio it will throw error (replace ? with parameter).

We use ? to pass parameter in execute sql task.So ? after EXEC does not make any sense and also =.

Hope this has answered your question.

Answer 6

Thanks yogish for your clarification.

---

Ramasubramanian S