Thursday, September 3, 2015

SSIS package loads DATA way too slow into a table via "OLEDB Destination" Data Flow Item

There might be many multiple reasons. Here is only one of them to fix:

Check value of "Access Mode" in "OLEDB Destination" Data Flow Item properties.

It Might be set as "OpenRowset". In that case SSIS inserts RBARs (Row By Agonizing Row).
On the one hand, SSIS spends a lot of time to send individual inserts for each processed rows.
On the other hand SQL Server writes all these inserts in database log.

If your database not under FULL Recovery Model you can switch to "Access Mode" to "OpenRowset Using FastLoad".
That directs SSIS to send as many records as you specified by "DefaultBufferMaxRows" parameter using BULK INSERT, which will not be recorded by SQL Server in database log.

That simple change can make your data load up to 1000 times faster.

No comments:

Post a Comment