The issue I've picked up is that if you accidentally use a SQL Server Destination in your Data Flow Tasks and you have to change your Destination to an OLE DB one, you have to sit and re-map your columns from scratch - Which is an extremely huge pain if you have hundreds of columns to map.
What you can try to do is the following:
* Right Click on your SQL Server Destination and click on Properties
* Change your MaxInsertCommitSize from 0 to X amount of rows.
In my mind this sort of defeats the use of the SQL Server Destination because it now would commit rows to your Database every time it hits the X amount of rows you have specified, but at least you wouldn't have to go and re-map all of your columns from scratch.
I'm not sure what the Maximum amount of rows are that you can change this to - So have a look on Google and see if it helps.