tofan.nayak (1/18/2012)

---

Trigger always fire when SSIS load or insert in SQl Server Database label or table base trigger

more.....

http://blog.sqlauthority.com/2009/05/27/sql-server-interesting-observation-of-logon-trigger-on-all-servers/%5B/quote%5D

That's for logon triggers. Setup a table trigger and try for yourself with an insert trigger.

CREATE TABLE SourceData

(SomeData VARCHAR(20))

INSERT INTO SourceData VALUES ('abc')

INSERT INTO SourceData VALUES ('def')

INSERT INTO SourceData VALUES ('ghi')

INSERT INTO SourceData VALUES ('jkl')

CREATE TABLE TargetData

(tdID INT Identity(1,1) NOT NULL,

SomeData VARCHAR(20))

CREATE TABLE DataLog

(tdID INT, DateOfEntry DATETIME DEFAULT (GETDATE()))

CREATE TRIGGER tr_i_TargetData

ON TargetData

FOR INSERT

AS

INSERT INTO DataLog (tdID)

SELECT tdID FROM Inserted

GO

INSERT INTO TargetData (SomeDAta) VALUES ('TestEntry')

SELECT * FROM DataLog

Now do a datapump in SSIS 2k5 from source to target and then recheck the DataLog table. The logging will not have fired.