How do I speed up a SSIS Transfer Server Objects task that runs really slow?

Question

Within SSIS 2005 I used the Import/Export wizard to create a package that drops/recreates and replaces the data on some tables between my production server and development machine. The control flow that was created by the wizard was extremely complicated so I created a new package and used the "Transfer SQL Server Objects Task" which is really easy to configure and setup as opposed to the crazy thing the wizard created. The problem is that the package that I created takes over 3 minutes to run while the wizard version takes about 20 seconds. They are basically doing the same thing, why such a difference in execution time and is there a setting that I can change in the package that is using the Transfer Objects task to make it run quicker?

Here is the package that the wizard created. I have created similiar packages before using the wizard that I had no problem editing, but I never saw anything like this before. I cannot figure out where to modify the tables and schema that I drop and create.

Here is the properties of the transfer task inside that for loop container

Answer 1

What connection type are you using?

Here when I've been wanting to transfer between Oracle and SQL, the ADO.NET provider is miles slower than the Oracle OLE DB provider.

Answer 2

Why not use the wizard generated package and figure out what it does? It is obviously doing things very efficiently.

Answer 3

Could be quite a number of things. Are you doing lookups? If so, use joins instead. You can also run a db profile to see what the crazy package does opposed to your custom package.

Answer 4

I don't use the wizard, but could it have created a stored procedure that will actually do the work? That would explain how it is going faster, since the stored procedure can do all the work within the database.

I am curious what is within TransferTask, as that seems to be where all the work is done.

You could look at exporting the data to a flat file, then using a Bulk Import to do this faster.

For some more thoughts about how fast things go look at here, but most important is some of the comments that were given, such as how he used Bulk Insert wrong.

http://weblogs.sqlteam.com/mladenp/articles/10631.aspx

UPDATE: You may want to also look at this: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-12 as, toward the end, he shows how long his tests took, but the first comment may be the most useful part, for speeding your import up.

Answer 5

This class of performance problem usually stems from "commit" levels and logging.

The illustrated wizard generated task does a "start transaction" before entering the loop and commits after all the data is transferred. Which is the best thing to do if the table is not 'enormous'.

Have you left 'autocommit" on in your hand coded version?