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:
CREATE TABLE DDLAudit
CREATE TRIGGER [trg_DDLAudit]
SET NOCOUNT ON
DECLARE @xEvent XML
SET @xEvent = eventdata()
INSERT INTO DDLAudit VALUES(
convert(datetime, convert(varchar(30), @xEvent.query('data(/EVENT_INSTANCE/PostTime)'))),
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)
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
create trigger trg_audit1 on testbulk
select * from inserted
To test that the trigger works, I ran the following queries. It works! :)
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.
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".
Ok, I cleaned data in both tables and ran the pack.
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:
I manually ran DROP TRIGGER and CREATE TRIGGER for the DDL trigger to see if I can catch the events.
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.