August 17, 2010 at 8:12 am
I am watching the load (from Visual Studio) and the numbers increase slowly.
I have a task that first truncates the table. Then I have a Data Flow Task (OLE DB Source and Destination) that just reads and writes the data from a sql server table to another sql server table.
The Data access mode is Table or view -fast load. How can I make this go faster? It took 30 minutes to load 2.2 million rows.
Also, when we load data from an SSIS package, does it write to the transaction log?
Thanks
August 17, 2010 at 1:12 pm
Please try increasing/decreasing the batch size else follow the below link -:
http://www.sqlbi.com/LinkClick.aspx?fileticket=svahq1Mpp9A%3D&tabid=169&mid=375
August 20, 2010 at 2:22 am
If the SSIS package runs on the same server as the destination table, you can use the SQL Server Destination, which is the fastest.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 23, 2010 at 2:00 am
You can try following:
1. Use fast load option on OLE DB destination, experiment with batchsize option and find your optimum number.
2. Drop the indexes on target table and recreate them after data loading. For large amount of data, dropping and recreating indexes is faster than inserting with indexes.
3. If the package is running on destination server itself, use SQL Server destination transform. Its considerably faster than anything else.
Hope this helps.
_____________________________________________________________
[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply