This is easy to do, see the example below.
1. Createthe stored procedure
CREATE PROOC procFitbitsDotComDotAu @Msg VARCHAR(50) OUTPUT
AS
SET @Msg = 'Fitbits.com.au - Australian SQL Server specialist'
RETURN 123
GO
2. Create DTS package with 2 global variables gvRC (integer) and gvMsg (string)
3. Create an Execute SQL Task with the following TSQL script:
SET NOCOUNT ON
DECLARE @rc INT
DECLARE @Msg VARCHAR(500)
EXEC @rc = procFitbitsDotComDotAu @Msg OUTPUT
SELECT @rc, @Msg
Specify output parameters as "Row Value" with bind variables gvRC and gvMsg.
4. Run the task and inspect global variables - they should have output values assigned within the stored procedure.
Hope this helps,
Anatol Romanov
http://www.fitbits.com.au