SSIS Execute SQL Task Out Parameter of -1 not showing up right

Question

This is the goofiest thing I've seen all day.

In SSIS 2005, I have an Execute SQL Task which runs a SQL 2005 stored proc that takes two IN parameters and one OUTPUT parameter. The IN parameters are static and so are hard-coded in the command string. The OUTPUT parameter is pulled into a package variable of type Int32 (although in the Execute SQL Task on the Parameter Mapping page it tells me the data type is LONG).

When I run the SQL Task and the output parameter is returning a value > 0 (like 2), the variable is populated with 2. When I run the SQL task and the output parameter is returning -1, the package variable is populated with some value like 66682316. I can run the proc in SSMS and if the value is pre-populated with -1, it returns -1 to me.

DECLARE @out int

SET @out = -1

EXECUTE MyProc 'param1', 'param2', @out OUTPUT

SELECT @out    -- returns -1

Does anyone have any idea why it would be returning this value instead of -1? I'm sure my variable is Int32 and not UInt32.

Answer 1

Shoudl you be saying

SELECT ? = @Out

Answer 2

If you set up your sql command like you did, you should be setting your variable from the result set not from the parameters.

Set Result Set to Single Row, then on the result set tab put 0 (if you are using OLEDB) as your result name and your variable (i.e. User::OutputVariable) as your variable name.

If you want to use parameters, you would set your sql up like this:

EXECUTE MyProc 'param1', 'param2', ? OUTPUT 

Then you would go to the parameter mapping tab and set up your parameter as follows:
Variable Name -> User::OutputVariable
Direction -> Output
Data Type -> Long
Parameter Name -> 0
Parameter Size -> -1

NOTE This applies to using OLEDB as the connection type on the general tab. How parameters are named is different depending upon connection type used.