Connection Type(OLEDB) Limitations When Using XML Data type in SQL Task

• selvaonline

  SSC Rookie

  Points: 37

  More actions

  December 29, 2010 at 10:46 am

  #388519

  You cannot vote on your own post

  0

  I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:

  SELECT (

  SELECT

  Emp.EmpId 'EmpId',

  Emp.EmpName 'EmpName',

  FROM Employee Emp

  ORDER BY Emp.EmpId desc)

  FOR XML PATH('EmpName'),Root('Employee'), TYPE

  If I try to use OLEDB connection I get the following error:

  <ROOT><?MSSQLError HResult=\"0x80004005\" Source=\"Microsoft XML Extensions to SQL Server\" Description=\"No description provided\"?></ROOT>

  Can anyone tell me why the SELECT ... FOR XML PATH... is not working with OLEDB Connection?

  Thanks in advance all ! Kindly help me on this pls.

  Regards,

  Selva

  http://www.selvaonline.com

• Koen Verbeeck

  SSC Guru

  Points: 259197

  More actions

  December 29, 2010 at 12:04 pm

  #1267373

  Did you set the ResultSet of the Execute SQL Task to XML?

  This blog post could be useful:

  http://consultingblogs.emc.com/jamiethomson/archive/2006/07/11/SSIS_3A00_-Output-a-file-as-a-csv-or-an-XML-file.aspx

  Especially read the comments.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP