SSIS speed loading Issue with "Table or view - Fast Load" option in the OleDBDestination component

Question

SSIS speed loading Issue with "Table or view - Fast Load" option in the OleDBDestination component

bouazizimedridha

SSC Enthusiast

Points: 180
More actions
January 30, 2015 at 7:14 am

#150323

Hi,
Description :
I opened an existing package in visual studio 2012, and noticed on the OleDBDestination component that the selected DataAccessMode was "Table or View". The duration of the load for 150 000 lines was of about 45min.
I switched the DataAccessMode to "Table or View - Fast Load", and the duration went down to 4 minutes.
Now the problem is :
Once I deploy the package to the SSIS server, it still takes 45 minutes, same as the original version of the package.
Could it be that the option does not get set or so. Has this happened to anyone before.
Thanks in advance !

Viewing 4 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

bouazizimedridha (1/30/2015)
Hi,
Description :
I opened an existing package in visual studio 2012, and noticed on the OleDBDestination component that the selected DataAccessMode was "Table or View". The duration of the load for 150 000 lines was of about 45min.
I switched the DataAccessMode to "Table or View - Fast Load", and the duration went down to 4 minutes.
Now the problem is :
Once I deploy the package to the SSIS server, it still takes 45 minutes, same as the original version of the package.
Could it be that the option does not get set or so. Has this happened to anyone before.
Thanks in advance !

Apologies for asking, but are you certain that the version you deployed is the same as the one which is being executed?

bouazizimedridha SSC Enthusiast Points: 180 More actions · Answer 2

Indeed,

I just did.

I deployed again, although I am pretty sure I redeployed the new version.

Below is the xml corresponding to the deployed file.

<property

dataType="System.Boolean"

name="FastLoadKeepIdentity">false</property>

<property

dataType="System.Boolean"

name="FastLoadKeepNulls">false</property>

<property

dataType="System.String"

name="FastLoadOptions">TABLOCK,CHECK_CONSTRAINTS</property>

<property

dataType="System.Int32"

name="FastLoadMaxInsertCommitSize">2147483647</property>

Thanks

Nevyn SSChampion Points: 14210 More actions · Answer 3

I'm with Phil in that it seems strange that it would be right around 45 mins after deploying the change, and that I would first go through everything to make sure I was running the new version. Is this project deployment model (deployed to a catalog)? If so, which is the active version of the project you deployed?

Once you get beyond that, you can look at tweaking your MaxInsertCommitSize depending on your packages requirements.

If you don't have a need to roll back all inserts if there is a single error, you may want to take that down to 10000 or so. Then the inserts will commit transactions in batches instead of waiting for all inserts and then committing one giant transaction.