I'm trying to populate an XML result set from an execute sql task which I then use to populate a HTML page. The SQL involved using for xml auto works fine in ssms but I can't get it to work in SSIS 2008. The only clue is that, when I click on build query in the execute SQL task it states "The EXEC SQL construct or statement is not supported." I initially populated the SQL query from a variable but it stated something along the lines of "Compound queries are not allowed". I tried converting the SQL command therefore to a stored procedure and attempted to call that from the Execute SQL task but with the above result.

My execute sql task has:

ResultSet = XML
ConnectionType = OLE DB
SQLSourceType = Direct input
SQLStatement = EXEC [dbo].[sp_GetSSISPackageLog] @executionid = ?

And in parameter mapping:

Variable= System::ExecutionInstanceGUID
Direction = Input
Data Type = NVARCHAR
Parameter Name = 0
Parameter Size = -1

Any ideas where I'm going wrong please?

I see IsQueryStoredProcedure is False but also grayed out for some readon, if that's any indication.