Post reply

SSIS package runs slowly

  • March 13, 2008 at 1:06 am

    #120764

    Hi,

    I need to transfer the data in A table on a 2005 instance to B table which has the same structure as A table on a 2000 instance. There are 200,000 records in A table. If I run "insert B select * from linkedserver.database.dbo.A", it takes only 30 seconds. I create a SSIS package to do this. But it is very slow. After it runs 10 minutes I have to stop it. And I find that it transfers about 100 records every second. Then I change the source server and destination server. That is transferring the same data from the 2000 instance to the 2005 instance. It takes only 50 seconds. why? How to make the package used for transfer data from the 2005 instance to the 2000 instance run fast?

    Thanks!

  • March 13, 2008 at 7:33 pm

    #789390

    Are you running any transformations on your data between source and destination? Are there any possible network connectivity issues between your workstation and either the source or destination server?

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • March 14, 2008 at 12:32 am

    #789467

    Are you running any transformations on your data between source and destination? Are there any possible network connectivity issues between your workstation and either the source or destination server?

    -----------------------

    there is no transformations in the package. the package is on the source server. I remote to the source server from my PC and execute the package. and there is no connectivity issues between the source and the destination.

    Thanks!

  • March 14, 2008 at 12:59 am

    #789474

    I have fixed the problem. In “data access mode” item, I should choose “table or view—fast load” but not “table or view”.

    Thanks for your reply!

  • March 16, 2008 at 8:07 pm

    #790176

    Just another thing here. For some reason, if you need to use a query to do something, please use Execute SQL task rather than the OLE-DB command in the dataflow. It tends to be faster using the execute SQL task. Just for your info 😀

    [font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

  • March 16, 2008 at 8:16 pm

    #790179

    I get it. Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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