April 5, 2012 at 7:47 am
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
------------------------------
"
April 10, 2012 at 12:57 am
What if you removed SET ARITHABORT ON from the query?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 29, 2014 at 7:23 am
Has anyone found a solution to this? I am running into exactly the same thing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply