SSIS Tutorial Part 16 - How to use OLE DB Destination with Trigger in SSIS Package

In this video you will learn how to load the data to SQL Server Table on which Insert Trigger is created. You will learn how to run the Trigger or not run the trigger when you are inserting data to Table from SSIS Package.

This Video can also be used to answer SQL Server Integration Services (SSIS ) Interview Question "
 If there is trigger on a table and we are loading data in that table by using SSIS, Access Mode properties is set to “ Table or View-fast Load”  , Will trigger fire? If not, then which Mode(
Table or View,
Table or View-Fast Load,
Table Name or view name variable,
Table name or view name variable –fast load,
SQL Command) can we  choose to load data and Trigger get fire as well?
"

Scripts used for Demo are here

CREATE TABLE dbo.SalePerson
(
  SalePersonID INT Primary Key,
  FirstName VARCHAR(25),
  LastName VARCHAR(25),
)
GO

Select * into dbo.SalePersonHistory from dbo.SalePerson


CREATE TRIGGER dbo.Trg_InsertSalePerson
ON dbo.SalePerson
AFTER INSERT AS
BEGIN
INSERT INTO dbo.SalePersonHistory
SELECT * FROM INSERTED
END
GO

insert into dbo.SalePerson values (1,'Aamir','Shahzad')

Select * from dbo.SalePerson
Select * from dbo.SalePersonHistory


Truncate table dbo.SalePerson
truncate table dbo.SalePersonHistory



No comments:

Post a Comment