Turning on TF610 in SSIS

Question

I have a simple SSIS package that loads a dummy file that contains 100000 rows. Each row is around 4k long, one int column and one long text column. I'm trying to test TF610 when loading this data into a table with a clustered index.

In my SSIS package, my Control Flow has a Execute SQL task to enable TF610, then on success go to my Data Flow Task which loads the flat file into the table. Both the Execute SQL and OLE DB Destination use the same Connection.

If I start a profile while running the SSIS package, and watch the commands, I can see DBCC TRACEON(610) executed then the INSERT BULK operations begin to fire. They both are using the same PID, so I'm assuming it's the same session.

When I check the log record length though, the insert is NOT being minimally logged.

If I enable TF610 globally and run the same SSIS package though the transaction is minimally logged.

I must be doing something wrong when turning on the TF610 in my SSIS package but I can't figure out what...

Answer 1

And so to force the same session to be used, in your connection manager, under the Properties settings, you can set the RetainSameConnection to True to force a single connection for your package versus using a pool.

Answer 2

Just because it's reusing the same session id doesn't mean your trace flag is still set. If SSIS has disconnected and reconnected it may get the same SPID but it's no longer the same session. I can reproduce this easily in SSMS by opening a new query window, setting the trace flag, and quickly closing the window and opening a new one (I get the same spid). When I check the flag using DBCC TRACESTATUD(610);, it is no longer set.

You may have to find a way to set the trace flag inside the Data Flow Task, or set it globally for the duration of the package.

DBCC TRACEON(610, -1);