SSIS Return few output xml parameters

Question

In SSIS (sql server 2008) I have a Sql task which is calling my stored procedure. The stored procedure gets 1 input parameter and return 2 output parameters. This is the prototype of my SP:

declare spGetPersonDetails(personid int, @orders xml output, @names xml output ) as.....

The problem is that in my sql task in iis, i declared these 3 parameters. one input and 2 outputs, and when it's running only one of the output parameters gets a value from the sp.

Any idea?

Thanks.

Jason Zambouras Jason Zambouras · Accepted Answer · 2012-02-16T22:11:42

I tried recreating this simply with a sample stored procedure.

Stored Procedure

CREATE PROCEDURE [dbo].[TestProcedure]
    -- Add the parameters for the stored procedure here
    @Input INT,
    @Output1 INT OUTPUT,
    @Output2 INT OUTPUT
AS
BEGIN
    SET @Output1 = @Input + 1
    SET @Output2 = @Input + 2
END

Then on the SQL task (which I used an OLE DB source) I had the sql statement set to

EXEC dbo.TestProcedure @Input = ?, @Output1 = ? OUTPUT, @Output2 = ? OUTPUT

Lastly all that was needed is valid parameter mapping ensuring the "Direction" column was set accurately.

I got most of this information from two sites:

http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx

http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters

I hope this helps you, if you have more information with the specific values you have set on this SQL task I will be happy to update my answer.

SSIS Return few output xml parameters

1 Answers