June 10, 2010 at 5:41 pm
I'm getting some errors trying to utilize the fast load option on an SSIS Data Flow into a multiple table view with an instead of trigger. Everything works without fast load, its just excruciatingly slow. I greatly appreciate any help/ideas!
My source database has a single table with a lot of duplicated data and empty fields. My destination database has several tables which do a much better job of breaking everything up. I have a view on the destination database which combines all of the tables and has an instead of insert trigger that correctly breaks down the input into the sub-tables providing the correct key references. I'm trying to get an SSIS Data Flow object that does a fast load from the source table into the destination table view. I've added the FIRE_TRIGGER option to the FastLoadOptions, but I'm getting an error about how it can't perform a SET operation. From here I think an example of the tables, views and triggers is in order...
First the source table:
CREATE TABLE source
(
IDint identity(1,1) NOT NULL,
deviceIDint,
fNamevarchar(100),
lNamevarchar(100),
msgTimedatetime,
msgTextvarchar(300),
latfloat,
longfloat,
primary key (ID)
)
Then the destination tables, views, and triggers:
CREATE TABLE destDevices
(
deviceIDint NOT NULL,
fNamevarchar(100) NULL,
lNamevarchar(100) NULL,
PRIMARY KEY (deviceID)
)
go
CREATE TRIGGER trg_destDevices_Insert ON destDevices
INSTEAD OF INSERT
AS
begin
update old
set fName= case when new.fName is null then old.fName else new.fName end,
lName= case when new.lName is null then old.lName else new.lName end
from inserted new
join destDevices old
on new.deviceID=old.deviceID
and (isnull(old.fName,'')!=isnull(new.fName,'')
or isnull(old.lName,'')!=isnull(new.lName,''))
insert into destDevices(deviceID, fName, lName)
select new.deviceID, new.fName, new.lName
from inserted new
left join destDevices old on new.deviceID=old.deviceID
where old.deviceID is null
end
go
CREATE TABLE destMain
(
IDint identity(1,1) NOT NULL,
deviceIDint NULL,
msgTimedatetime NULL,
msgTextvarchar(300) NULL,
PRIMARY KEY (ID),
FOREIGN KEY (deviceID) REFERENCES destDevices(deviceID)
)
go
CREATE TABLE destSub
(
IDint NOT NULL,
latfloat NULL,
longfloat NULL,
PRIMARY KEY (ID),
FOREIGN KEY (ID) REFERENCES destMain(ID)
)
go
CREATE VIEW destAll
AS
SELECT m.ID, m.deviceID, d.fName, d.lName, m.msgTime, m.msgText, s.lat, s.long
FROM destMain m
left join destSub s on m.ID=s.ID
left join destDevices d on m.deviceID=d.deviceID
go
CREATE TRIGGER trg_destAll_Insert ON destAll
INSTEAD OF INSERT
AS
begin
INSERT INTO destDevices
SELECT DISTINCT deviceID, fName, lName
FROM inserted
DECLARE @tbl table(ID int, deviceID int, msgTime datetime)
INSERT INTO destMain(deviceID, msgTime, msgText)
OUTPUT inserted.ID, inserted.deviceID, inserted.msgTime INTO @tbl
SELECT deviceID, msgTime, msgText
FROM inserted
INSERT INTO destSub(ID, lat, long)
SELECT m.ID, new.lat, new.long
FROM @tbl m
join inserted new on m.deviceID=new.deviceID
and m.msgTime=new.msgTime
end
go
And Finally, some sample source data:
INSERT INTO source(deviceID, fName, lName, msgTime, msgText, lat, long)
SELECT 1, 'Joe', 'Smith', '2010-06-01 12:00', 'one', null, null UNION
SELECT 1, 'Joe', 'Smith', '2010-06-01 12:01', 'two', null, null UNION
SELECT 1, 'Joe', 'Smith', '2010-06-01 12:02', 'three', null, null UNION
SELECT 1, 'Joe', 'Smith', '2010-06-01 12:03', 'four', null, null UNION
SELECT 1, 'Joe', 'Smith', '2010-06-01 12:04', 'five', 12.3456, 123.4567 UNION
SELECT 1, 'Joe', 'Smith', '2010-06-01 12:05', 'six', null, null UNION
SELECT 2, 'Jane', 'Doe', '2010-06-01 12:01', 'one', null, null UNION
SELECT 2, 'Jane', 'Doe', '2010-06-01 12:03', 'two', 34.4567, 89.0123 UNION
SELECT 2, 'Jane', 'Doe', '2010-06-01 12:04', 'three', 34.4568, 89.0124 UNION
SELECT 2, 'Jane', 'Doe', '2010-06-01 12:06', 'four', null, null
The exact error (as duplicated using the above sample structure) is:
[OLE DB Destination [55]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "'dbo.destAll' is not a user table. Cannot perform SET operation.".
Again, any insight will be greatly appreciated... I think I've banged my head enough...
Thanks,
--Adam
June 11, 2010 at 9:18 am
Well, I think I found a work around... If [destAll] is defined as a table, and not a view, and nothing else is changed, everything seems to execute correctly.
So, instead of CREATE VIEW destAll
AS
SELECT m.ID, m.deviceID, d.fName, d.lName, m.msgTime, m.msgText, s.lat, s.long
FROM destMain m
left join destSub s on m.ID=s.ID
left join destDevices d on m.deviceID=d.deviceID
I can useCREATE TABLE destAll
(
deviceIDint,
fNamevarchar(100),
lNamevarchar(100),
msgTimedatetime,
msgTextvarchar(300),
latfloat,
longfloat
)
Now I just need to figure out what was actually causing the error in the first place!
--Adam
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply