I'm trying to retrieve a value from a stored procedure I'm running on a MS SQL Server database.

I've added an Exec SQL Task, which I've defined as:

exec niisp_HasMovedFileToday ?,?,?

The stored procedure is defined in the database as:

=========
CREATE PROCEDURE niisp_HasMovedFileToday (@s_year varchar(4), @s_month as varchar(2), @s_day as varchar(2))AS

DECLARE
@s_result as varchar (8)

select @s_result = DateKey from FileCheckHistory where DateKey = @s_year + @s_month + @s_day and FileThere = "Y"
if (@s_result is null)
begin
return -1
end

return 0
===========

I had no trouble setting up the input parameters from global variables in my DTS package.

However, I can't quite figure out the Output parameter tab.

It seems I can define one of 3 types:

(1) None
(2) Row Value
(3) Row Set

I would assume that I wanted the Row Value, and that I would get to pick a global variable to put the value in, but the
space for that is greyed when I select that option.

It ungreys if I pick Row Set, but if I then run the package step the global variable I select shows up as
"undisplayable" when I look at via the package properties.

Any advice appreciated.

I've actually since figured out a better way to accomplish my task, but am still interested in how you get values back
from a stored procedure called in this fashion.

what value you want to be returned?

Something like this will work, i use Northwind as an example

Select CompanyName from customers where customerid = 'ALFKI'

Then you should be able to assiang the companyname into a globalvariable by
selecting Row Value

refer to the folloing link if you want a output value from a stored procedure
http://www.sqldts.com/default.aspx?234

Ed

"John Baker" wrote: