Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Optimize Transfer SQL Server Objects Task

Author  Topic 

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2013-04-26 : 06:42:14
Hi all,

Am using presently using Transfer SQL Server Objects Task in my etl process.

It takes like 6 hours for the task to complete, which is outrageous. I need help on how I can optomize the control or a suggestion on another approach.

Thanks.

'Abacus

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 06:43:41
which all objects you're trying to transfer? whats the amount of objects involved?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2013-04-26 : 07:01:58
Hi Visakh16,

All in total about 6 items, but they are very large transaction tables. Minimum of 2millions rows.

I sign for fame not for shame but all the same, I sign my name.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 07:43:19
6 items and 6 hours?? thats really on high side

Why do you've to transfer objects in etl? can you give us some context on what you're trying to do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2013-04-26 : 08:49:20
There are two servers. Server A is the process engine, while server B is the report portal. After every scheduled processing, another etl moved the processed data from Server A to B.

For smaller tables, I use Transfer SQL Server Objects Task to move them, drop and create..
and for large table, I used delete and insert.

Thanks.

I sign for fame not for shame but all the same, I sign my name.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 01:23:01
hmm...can you try using a ssis package to truncate and load the tables instead?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -