CODEWITHSUNDEEP
xmlssisresultset
tom33pr
tom33pr

Reputation: 1013

SSIS 2005 Multiple XML documents

Folks,

Is there any way I could return two separate XML documents in one 'Execute SQL Task'?

1) Create a stored procedure that returns two separate XML documents 2) Configure the 'Execute SQL Task' so it has: - ADO.NET connection manager, - it calls the stored procedure, - it has resultSet set to 'XML', - and on the 'Result Set' lever the output is mapped to two separate string variables: e.g.: ResultName: 0 - VariableName: firstVar, ResultName: 1 - VariableName: secondVar;

It all work fine if I have the 'Result Set' with only one result variable - it nicely returns the first xml from the called stored procedure and maps it to the variable. When I add the second variable i get the error:

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".

Is this possible to achieve this one task? Thank you in advance.

Upvotes: 1

Views: 158

Answers (1)

Edmund Schweppe
Edmund Schweppe

Reputation: 5132

Not directly. As noted in MSDN:

  • The XML result set is used when the query returns a result set in an XML format. For example, this result set is used for a SELECT statement that includes a FOR XML clause.

If the Execute SQL task uses the Full result set result set and the query returns multiple rowsets, the task returns only the first rowset. If this rowset generates an error, the task reports the error. If other rowsets generate errors, the task does not report them.

As you noted, however, you could write a stored procedure which ran your two queries, stored the results internally as strings, and then returned the results as OUTPUT parameters. Or, of course, you could just build two Execute SQL tasks.

Upvotes: 1

Related Questions