Problem with Execute SQL task populating an XML result set
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:
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.
Tags for this Thread
DatabaseJournal Recent Articles
Executing SSIS Packages Using DTExec
What Every DBA Ought to Know About SQL Ser...
Redmond exploits MySQL uncertainty
Oracle Launches Oracle Global Trade Manage...
SQL Server 2008 RTM Support Ends April 13,...