CODEWITHSUNDEEP
ssis
Dinesh
Dinesh

Reputation: 303

how to insert data from table to view using ssis 2008 R2?

While importing data i am getting error corresponding view not available in the db. But i checked in the DB that view is available . I tried manually insert into view that time its working.

Any setting to change for this?

While data importing from table to view via SSIS . I 'm getting following error "Invalid object name [Feed].[VwPharmaClaim]." This already exists. I traced the query in the profiler Quotes surrounded with the object name as ("[Feed]"."[VwPharmaClaim]").Without the quotes its working.

While I'm inserting data in 2008 version I am getting following error

Msg 208, Level 16, State 1, Line 1 Invalid object name '[Feed].[VwPharmaClaim]'

I traced the insert query in profiler

exec sp_executesql N'INSERT INTO "[Feed]"."[VwPharmaClaim]" ("PharmaKey","member_id","MemberRefID","claim_id","ndc","days_supply","refill_number","fill_dt","paid_dt","prescriber_id","allowed_amt","paid_amt","Member_amt","quantity","Import_Id","age") VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16)',N'@P1 bigint,@P2 varchar(8000),@P3 numeric(18),@P4 varchar(8000),@P5 varchar(8000),@P6 numeric(10),@P7 int,@P8 datetime,@P9 datetime,@P10 varchar(8000),@P11 money,@P12 money,@P13 money,@P14 float,@P15 numeric(18),@P16 int',1,'000000000052',52,'085565501321 ','00472030115',5,0,'2008-06-04 00:00:00','2008-06-08 00:00:00','BO5586396 ',$10.0000,$0.0000,$10.0000,15,7,5368

OUTPUT Msg 208, Level 16, State 1, Line 1 Invalid object name '[Feed].[VwPharmaClaim]'.

When I remove the double quotes it’s working fine

exec sp_executesql N'INSERT INTO [Feed].[VwPharmaClaim] ("PharmaKey","member_id","MemberRefID","claim_id","ndc","days_supply","refill_number","fill_dt","paid_dt","prescriber_id","allowed_amt","paid_amt","Member_amt","quantity","Import_Id","age") VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16)',N'@P1 bigint,@P2 varchar(8000),@P3 numeric(18),@P4 varchar(8000),@P5 varchar(8000),@P6 numeric(10),@P7 int,@P8 datetime,@P9 datetime,@P10 varchar(8000),@P11 money,@P12 money,@P13 money,@P14 float,@P15 numeric(18),@P16 int',1,'000000000052',52,'085565501321 ','00472030115',5,0,'2008-06-04 00:00:00','2008-06-08 00:00:00','BO5586396 ',$10.0000,$0.0000,$10.0000,15,7,5368

OUTPUT

(1 row(s) affected)

My package developed 2005 . I have converted this package to following version in 2008.

version 9.0.30729.4462.QFE

This My error while importing data from table to view

[OLE DB Destination [185]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E37 Description: "Invalid object name '[Feed].[VwPharmaClaim]'.".

Upvotes: 0

Views: 970

Answers (2)

user3550767
user3550767

Reputation: 1

I experienced this with SSIS 2013 and trying to insert to an updatable view. This view uses an 'instead of' trigger to do the insert.

In order for it to work, I had to go into the advanced editor for the destination and on the component properties tab remove the square brackets from the OpenRowset, so [dbo].[myView] becomes dbo.myView. Also, the AccessMode needs to be plain OpenRowset; fastload does not work.

Upvotes: 0

Kyle Hale
Kyle Hale

Reputation: 8120

By default in SQL Server 2008 you are not allowed to use double quotes to identify database objects.

Run 

ALTER DATABASE <dbname> SET QUOTED_IDENTIFIER = ON

against your destination database and then try running 

SELECT * FROM "Feed"."VwPharmaClaim"

Check out http://technet.microsoft.com/en-US/library/ms174393(v=sql.105).aspx for some more info.

When you say SSIS is generating these queries, what sort of task are you using to generate them? I've never seen it produce double quote identifiers before, just curious.

Upvotes: 1

Related Questions