Im not sure but Im going mad, seems pretty clear from the hords or articles I've read that MICS controls when the BULK INSERT should commit the transaction causing TLOG not to grow uncontrollably.
But in my scenario I have
In OLEDB Destination I have tried all kinds of combinations of
Rows_Per_Batch and MICS but nothing is giving me the desired result of my TLOG not growing like crazy and BULK INSERT inserting all 11,5M rows in ONE BIG TRANSACTION.
My TLOG grows to 43GB which is now giving me issues on the PROD server as it runs out of space.
I assume a configuration of OLEDB Destination with:
Rows_Per_Batch = 11 500 000 should help optimizer know how much data the bulk insert is expected to handle (I have tried 10000 as well same result, TLOG grows).
MICS = 100000 (This setting seems to do nothing, in Profiler I see the BULK INSERT query but it is missing the BATCHSIZE option which should control the commit)
It seems obvious the MICS should be the anser but anything I tried I cant get it to not do entire load in one big transaction.
Any clarification on where in going wrong would be very welcome.