Connection Type(OLEDB) Limitations When Using XML

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)

Connection Type(OLEDB) Limitations When Using XML

Author

Topic

selvaonline
Starting Member

1 Post

Posted - 2010-12-29 : 13:12:15

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.

latch
Yak Posting Veteran

62 Posts

Posted - 2010-12-30 : 09:28:58

Hi,

1. Use an ADO.Net datasource for the Execute SQL Task
instead of the OLEDB connection.

2.Set up ResultSet of the execute SQL Task to "Full Resultset" mode, and map a single ResultSet to a variable of type "Object" accordingly.

3.Set up a ForEach loop to enumerate over an ADO Enumerator. Set enough Variable Mappings to correspond to each column of the result set.

For more info check:

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-ssis/1954/SSIS-Any-Experience-With-SQL-Task-Returning-XML

Thanks,
Latch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources