SSIS Exec SQL task and return parameters

[Datasort]

I am trying to receive the return value (@ReturnValue) from a stored proc
while using the SSIS Execute SQL command and map it to a package variable.


I can run in a SQL query window the following commands

DECLARE @ReturnCode INT

exec @ReturnCode = usp_Import_Entries 'EMD'

Print @ReturnCode

This will return an integer.

I have tried to run the query in SSIS execute task without any luck. Here
is what I am sending as the task command:

exec ? = usp_Import_Entries 'EMD'

and I have set up a paramater mapping with the following option


Variable name: User::iSP_EntryReturn
Direction: ReturnValue
Data Type: Long
Parameter Name: @ReturnCode

I have tried to declare User::iSP_EntryReturn as an int16, int32, and int64
with no luck

When I run the proc the following error occures:


Error: 0xC002F210 at Load Entries Table, Execute SQL Task: Executing the
query "exec ? = usp_Import_Entries 'EMD'" 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, parameters
not set correctly, or connection not established correctly.


Also note that I can run the task with the following command without
problems as long as I do not have any parameter mappings.

exec usp_Import_Entries 'EMD'"

Any thought on how I can get the return value stored from the task?

Thanks in advance,

[Allan Mitchell]

Hello Datasort,

have a look here. We do pretty much everything with this task

The ExecuteSQL Task
(http://www.sqlis.com/default.aspx?58)

allan

proc while using the SSIS Execute SQL command and map it to a package
variable.
I can run in a SQL query window the following commands
This will return an integer.
I have tried to run the query in SSIS execute task without any luck.
exec ? = usp_Import_Entries 'EMD'
and I have set up a paramater mapping with the following option
Variable name: User::iSP_EntryReturn
Direction: ReturnValue
Data Type: Long
I have tried to declare User::iSP_EntryReturn as an int16, int32, and
int64 with no luck
Error: 0xC002F210 at Load Entries Table, Execute SQL Task: Executing
the query "exec ? = usp_Import_Entries 'EMD'" 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, parameters not set correctly, or
connection not established correctly.
Also note that I can run the task with the following command without
problems as long as I do not have any parameter mappings.
exec usp_Import_Entries 'EMD'"
Any thought on how I can get the return value stored from the task?
Thanks in advance,

[Datasort]

Thanks to Allan Michell site at (http://www.sqlis.com/default.aspx?58) I was
able to figure out the problem.

The issues is that the last value

Parameter Name:

should read 0 not @ReturnCode. I assume this is a zero based array.
--
Stewart Rogers
DataSort Software, L.C.

Post by Datasort
while using the SSIS Execute SQL command and map it to a package variable.
I can run in a SQL query window the following commands
This will return an integer.
I have tried to run the query in SSIS execute task without any luck. Here
exec ? = usp_Import_Entries 'EMD'
and I have set up a paramater mapping with the following option
Variable name: User::iSP_EntryReturn
Direction: ReturnValue
Data Type: Long
I have tried to declare User::iSP_EntryReturn as an int16, int32, and int64
with no luck
Error: 0xC002F210 at Load Entries Table, Execute SQL Task: Executing the
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
Also note that I can run the task with the following command without
problems as long as I do not have any parameter mappings.
exec usp_Import_Entries 'EMD'"
Any thought on how I can get the return value stored from the task?
Thanks in advance,
--
Stewart Rogers
DataSort Software, L.C.