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?
Problem with Execute SQL task populating an XML re
Posted - 04/13/2012 : 10:31:27
