June 24, 2010 at 3:11 am
Hi,
I am trying to stream data in a dataflow to an OLE DB Destination. I try to copy the data to a view with an INSTEAD OF trigger that decides if an updated is to be made or an insert:
(I am using SQL 2005, so don’t have the MERGE statement yet)
SET NOCOUNT ON
GO
CREATE TRIGGER ti_impProduitConditionne
ON imp.ProduitConditionne
INSTEAD OF insert
AS
BEGIN
-- Update those whose ID already exists
UPDATE Prod.ProduitConditionne
SET StatutCommercial = i.StatutCommercial,
Usage = i.Usage,
DenominationBi = rtrim(i.DenominationBi),
DenominationFr = rtrim(i.DenominationFr),
DenominationNl = rtrim(i.DenominationNl),
CodeLegislation = i.CodeLegislation,
TemperatureConservation = i.TemperatureConservation
FROM inserted i
WHERE ProduitConditionne.ID = i.ID
-- Insert new ones
INSERT INTO Prod.ProduitConditionne (ID, CNK, StatutCommercial, Usage, DenominationBi, DenominationFr, DenominationNl, CodeLegislation, TemperatureConservation)
SELECT i.ID, i.CNK, i.StatutCommercial, i.Usage, i.DenominationBi, i.DenominationFr, i.DenominationNl, i.CodeLegislation, i.TemperatureConservation
FROM inserted i
LEFT OUTER JOIN Prod.ProduitConditionne p
ON (i.id = p.id)
WHERE p.id IS NULL
END
GO
When I use the option Data access mode: “Table or view – fast load” I get duplicate key errors. I suppose there is a bulk copy or a check that is unaware that the duplicates are transformed to updates.
Error: 0xC0202009 at Import ProduitConditionne from DOCUFAR, OLE DB Destination [20748]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_ProduitConditionne'. Cannot insert duplicate key in object 'Prod.ProduitConditionne'.".
Same thing with a SQL Server Destination
Error: 0xC0202009 at Import ProduitConditionne from DOCUFAR, SQL Server Destination [20660]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_ProduitConditionne'. Cannot insert duplicate key in object 'Prod.ProduitConditionne'.".
When I use the access mode “Table or view “ without “fast load”, I get another error:
Error: 0xC0202009 at Import ProduitConditionne from DOCUFAR, OLE DB Destination [20748]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.".
Error: 0xC0209029 at Import ProduitConditionne from DOCUFAR, OLE DB Destination [20748]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (20761)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (20761)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Import ProduitConditionne from DOCUFAR: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (20748) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Import ProduitConditionne from DOCUFAR: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
Has anybody an idea?
Thanks!
Jan
June 29, 2010 at 3:05 pm
I don't think you can use Fast Load in this case.
CEWII
June 29, 2010 at 3:13 pm
Eliott,
I agree. But why can't I use normal load?
Jan
June 29, 2010 at 3:45 pm
Yeah, you got me..
I looked at your trigger and it would seem to prevent dupes. I would write it differently though, I would check for record existence and then doing a insert or update.
Could there be more than one connection being made to the database? It would seem weird but I admit I'm reaching..
CEWII
September 8, 2010 at 2:02 pm
In the OleDB Destination properties, Add to: “Fast Load Options” property, type “FIRE_TRIGGERS”. This allows the SSIS insert to fire the INSTEAD OF trigger, on the view.
THANKS A MILLION for this tip to Adam Haines and see his blog UPSERT article at http://jahaines.blogspot.com/2009/09/sss-performing-upsert.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply