SSIS:Transfer SQL Object Task:Delay between Start of Task & Execution

[rackman99]

Hi,
I use a TSOT to copy approx 700 tables between one server and another as an overnight package. This data is set to replace and there are no other objects (views, etc) being copied. What I have noticed by looking at the event log, is that it takes about 1 and 1/2 hours between the package being started and the data to start copying over. In the progress tab it tells me that all the Tables alread exist in the destination (as expected), that there are no other objects to copy. Then it just sits there till the event log starts to record the table copies. I have tried changing it to a replace (thus removing the drop and recreate) and doing a Truncation on all the tables beforehand, but this has not made any differece. Any ideas on what causes this delay and how to remove it would be appreciated.

 

[NathanGriffiths]

Hi, Not sure why you are seeing such a long delay with the Transfer SQL Objects task, I haven't used much myself as I found it too fiddly to configure - this blog post below describes a way of creating your own object transfer using a Script Task that uses SQL Management Objects, which is basically what the TSOT does in the background.

http://blogs.msdn.com/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx

The documentation for the SMO Transfer class is here:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer(SQL.90).aspx

Hope that helps!

 

[rackman99]

Cheers, will give them a try