Error while using XML in SQL Command IN SSIS OLEDB SOurce

Question

Post reply

Error while using XML in SQL Command IN SSIS OLEDB SOurce

Sachin4all

Ten Centuries

Points: 1249
More actions
April 5, 2012 at 7:47 am

#391954

I am using the below query as the SQL Command in OLEDB Source in SSIS.

I need to use the CSV list in the destination.

declare @a table (agentid int, name varchar(10))

declare @C table (cityid int, name varchar(15))

declare @x table (agentid int, cityid int)

insert into @a

select 1, 'x'

union all select 2, 'y'

insert into @C

select 1, 'chicago'

union all select 2, 'bloomington'

union all select 3, 'st louis'

insert into @x

select 1,1

union all select 1,2

union all select 1,3

union all select 2,1

union all select 2,3

SET ARITHABORT ON

SET QUOTED_IDENTIFIER ON

;with combined as

(

select a.agentid, a.name agentname, c.name cityname

from @a a

join @x x on x.agentid = a.agentid

join @C c on c.cityid = x.cityid

)

select o.agentid, o.agentname,

STUFF((SELECT ','+ i.cityname

FROM combined i

WHERE i.agentid = o.agentid

ORDER BY i.cityname

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)')

, 1,1,'') cities

from combined o

group by o.agentid, o.agentname

But it works perfectly in SSMS 2005 but not is SSIS 2005.

I am getting following error

"TITLE: Microsoft Visual Studio

------------------------------

Error at Add Column to PCSPROD in ICASTAGING [PCS-- SRC Query [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.".

------------------------------

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------

BUTTONS:

OK

------------------------------

"