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 = XMLConnectionType = OLE DBSQLSourceType = Direct inputSQLStatement = EXEC [dbo].[sp_GetSSISPackageLog] @executionid = ?And in parameter mapping:Variable= System::ExecutionInstanceGUIDDirection = InputData Type = NVARCHARParameter Name = 0Parameter Size = -1Any ideas where I'm going wrong please? I see IsQueryStoredProcedure is False but also grayed out for some readon, if that's any indication.
IsQueryStoredProcedure is greyed out unless you choose ADO as the connection type.
Make sure you dont have any GO statements in your query.
Thanks for the reply Mike. There are no Go statements in the query unfortunately so this isn't the fix.