Post reply

SSIS - Deletion in batches

  • December 3, 2010 at 5:13 am

    #1258304

    Are you sure you need an SSIS package?

    Could you use something like this in T-SQL (the variable's not strictly necessary, just makes it easier to change):

    DECLARE @batchSize INT

    SET @batchSize=25000

    WHILE 1=1

    BEGIN

    DELETE TOP (@batchSize) FROM MyTableName

    WHERE IsDeleted = 1

    IF @@ROWCOUNT < @batchSize BREAK

    END

  • December 3, 2010 at 5:27 am

    #1258321

    If it does need to happen in SSIS, delete via the Execute SQL Task in Control Flow. Don't use the OLE DB SQL Component in the data flow task. As was previously mentioned, that works on a row-by-row basis and is incredibly slow.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply