Which Properties I should have in SSIS package to load data faster?

Question

Hi, i am loading 5 millions of records in a table which takes around 20 mins.

I have a stored proc which loads 5 millions of records in two tables which takes around 25 mins.

I have fastLoad in Oledb destination and access mode is set to open rowset using fastload but still you can see from the time its not performing well.

So wondering what are the other properties i should i have so that the the data load will be faster?

I am simply loading from OLEDB source to OLEDB destination.

---Just realised, there's something i can do dropping and creating constraints back on table.

Answer 1

Table access mode of Fast Load is probably your best bet. Also best to limit the incoming fields to ONLY what you need, which reduces the memory overhead of the data flow.

You can mess with the Maximum insert commit size but I would recommend AGAINST that. You need FULL understanding of the SSIS engine (which I don't) before changing that.

5 million in 20 minutes? Why is that not acceptable?

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

Answer 2

Ensure the Table Lock is set to on.

Indexes you can disable, but need to rebuild later.

make sure the buffer is large enough, the FastLoadMaxInsertCommitSize is large, and lastly checking constraints is not a good idea.

If you need more tips please tell how you load exactly, perhaps there is more to do.

---

Arthur My Blog

Answer 3

Hi,

If you have SSIS running on same box as destination, You can use sql server destination instead of OLEDB with fast load. SQL server destination loads data faster then the oledb with fast load.

You can also set the MaxBufferRows and MaxBufferSize property, but you need to do lot of experiments to find out ideal value for your environment.

Apart from this SSIS seetings, you can change the recovery mode of database to BULK-LOGGED (if acceptable to DBA).

---

- Chintak (My Blog)

Answer 4

Chintak hi,

I did not see it working any faster, IMHO should even never be used as it requires elevated rights.

The fast-load option is the BULK LOAD so it cannot be sped up any more.

---

Arthur My Blog

Answer 5

refer to Adam Tappis' comments here:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/86efc54e-45bd-4ccc-baf1-a7b34f80fe32/

I'd normally prefer a insert commit size of 10,000 - 50,000 for number of records you specified to prevent the sql engine to wait too long before it commits anything.

Answer 6

Hello Todd, thanks for your reply,

I am just curious since the proc loading 2 tables with the same no. of records is taking 25 mins.