Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

SSIS Package

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.

Profile

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...

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

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.

Retain same connection

share|improve this answer
    
This was it. Thank you! –  Brad Apr 5 '13 at 18:44
1  
+1 nice one. I can barely spell SSIS so there was no hope for me to know how to do that. (Never mind know that it would have such a stupid default.) –  Aaron Bertrand Apr 5 '13 at 18:56
1  
@AaronBertrand I wouldn't have been able to answer without your post to put me on the trail. I can't spell CBDD (see) –  billinkc Apr 5 '13 at 19:12
add comment

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);
share|improve this answer
    
Thanks Aaron. Bad assumption on my part... –  Brad Apr 5 '13 at 18:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.