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.
I don't think it's the syntax within the Execute SQL task that may be the problem. I've tried changing the text to execute dbo.dp_GetSSISPackageLog ? and I still get the syntax error "The EXEC SQL construct or statement is not supported.". Am I not able to put stored procedures with IF statements in them in to execute sql tasks? I suspect though it's more the "for xml auto, elements, type, root('SSISPackageLog')" statement in the stored procedure that may be the cause. Is this not allowed?