Problem with Execute SQL task populating an XML re

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Problem with Execute SQL task populating an XML re

Author

Topic

griffster
Starting Member

3 Posts

Posted - 2012-04-13 : 10:31:27

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 12:24:11

this is a good article to understand how to use options while calling stored procedure from ssis

http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

griffster
Starting Member

3 Posts

Posted - 2012-04-16 : 04:36:41

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 11:43:42

Am I not able to put stored procedures with IF statements in them in to execute sql tasks?

do you mean IF statements inside procedure or are you trying to conditionally call procedure using if statement inside execute sql task command ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources