This blog will no longer be updated.

New content is available on my new blog

DISABLE TRIGGER not captured, SSIS - Piotr Rodak

DISABLE TRIGGER not captured, SSIS

This post was to be about behavior of SSIS when they insert rows to tables with triggers on them. We had an issue with one of our packs that began to fail 'all of a sudden'. But when I began writing this post and building test SSIS project and database objects, I came across something even more interesting.

Here we go:

The pack was working properly for several months and all of a sudden one of the execution paths began to fail. I related this failure to change that I had made a few days before. I added trigger to one of the tables that was populated by the pack. The pack uuses bulk method to insert rows to the table. There are numerous resources about the issue: by default triggers are disabled during BULK INSERT. MSDN has a document about different bulk operation techniques and behavior of triggers. I found it interesting by the way, that OPENROWSET method treats triggers in opposite way to BULK INSERT and bcp. SSIS behaves like bcp when it comes to bulk operations. If you want to use triggers during bulk insert, you must add FIRE_TRIGGERS hint in the Advanced Editor of OLEDB destination.

Usually (when FIRE_TRIGGERS is not supplied), SSIS will disable triggers while inserting rows to a table using fast load option. To find out what is happening, I set up DDL trigger that records all DDL operation on test database. The code to create audit table and the trigger is shown below:

--–this table will store some information about user activity
CREATE TABLE DDLAudit
(
dttime datetime,
strservername VARCHAR(25),
strusername VARCHAR(15),
strchange VARCHAR(max)
)

GO

CREATE
TRIGGER [trg_DDLAudit]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
DECLARE @xEvent XML
SET @xEvent = eventdata()
INSERT INTO DDLAudit VALUES(
convert(datetime, convert(varchar(30), @xEvent.query('data(/EVENT_INSTANCE/PostTime)'))),
CONVERT(VARCHAR(25), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(15), @xEvent.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

GO

Since I have a CSV file with output of sys.dm_db_physical_index_stats DMV, I decided to create test table using code as follows:

select * into testbulk from sys.dm_db_index_physical_stats(db_id(), null, null, default, default)
where 1=0

GO

Then I created the history table and a (very simple) trigger for insert that copies the inserted rows from the work table testbulk to the history table.

select * into testbulkhistory from testbulk where 1=0

go

create
trigger trg_audit1 on testbulk
for insert
as
insert testbulkhistory
select * from inserted

To test that the trigger works, I ran the following queries. It works! :)

insert testbulk
select top 1 * from testbulk

select * from testbulkhistory

Ok, now it's time for the SSIS package. I created pretty simple pack that pulls data from CSV file and stores them in the testdb.dbo.testbulk table.

Data flow

I added Data Conversion and Derived Column components to adjust the data type, otherwise SSIS was complaining about conversion errors.

I set the data access mode to "Table or view - fast load".

Fast load data access mode set

Ok, I cleaned data in both tables and ran the pack.

delete testbulk
delete testbulkhistory

As expected, the DML trigger was disabled by SSIS. There were no data in testbulkhistory table. But I was surprised to see, that DDL trigger was not fired.

I launched Profiler to see what happens, but I didn't find any information. I added following events to the trace to see if I can catch the statement disabling DDL trigger:

Alter object events

I manually ran DROP TRIGGER and CREATE TRIGGER for the DDL trigger to see if I can catch the events.

Profiler trace

As you see, the events were captured correctly. I tried to capture also statements DISABLE TRIGGER and ENABLE TRIGGER, but these events are not covered by the Objects event category. There is a bug reported regarding DISABLE TRIGGER not being an event. I think it is not correct that there are two ways of disabling a DML trigger and only one of them is indirectly logged in ALTER TABLE statement. In case of DDL triggers, you don't have even the option to call 'logged' syntax, DISABLE and ENABLE TRIGGER events are just invisible to profiler.

If you think this is a problem, vote on this bug.

I wonder if there is a way to monitor what happens with the triggers during BCP/SSIS loads.

I attach test SSIS project and sample data, so you can test this yourself.


Published Saturday, October 10, 2009 10:45 PM by Piotr Rodak

Comments

# SSIS insert bulk of data into remote table with trigger « [Business Intelligence].[ALL]

Pingback from  SSIS insert bulk of data into remote table with trigger  «  [Business Intelligence].[ALL]