My Data Flow consists of an OLE DB Data Source, a Data Conversion Task and an OLE DB Command. I just ran the query in SQL Server Management Studio and it took 2 seconds to return 5 rows. My SSIS Package has now been 'asleep' in the Prepare for Execute phase for the past 46 minutes. Previously when I was retrieving 3500 rows, the SSIS package would hang on the Pre-Execute phase. I tried changing the packet size on the connection to 32767, but this didn't seem to change the speed. The source database is on a different network than I'm on. I won't be able to run profiler on it because it's on a server run by another company. Can you think of anything I might try to enhance the performance? I'm not too sure how to explain the situation to other employees because of the query running so quickly in SSMS, but horribly slow in SSIS. Thanks

What would you recommend setting the EngineThreads property to? It was set to 10, but I only have one data flow with one source and one destination. Would it make sense to set it to 2 instead of 10?

make sure you place the ssis package within the company's network? the speed of it will depend on the slowest part/component which is probably the network. use bulk export, compress, copy, decompress and import will probably work better if it needs to go over the network. hope this helps

I'm using SQL Server 2005 / VS 2008 unfortunately. I don't believe the bulk export task was available until SS 2008 / VS 2010.

Hello, You should also try switching to the ADO.NET (Datareader) Source, though it works pretty similar to the OLE DB Source it did give me performance gain with lesser number of reads to the database when compared to the OLE DB Source. You will also have to switch your connection to the Source as a ADO .NET Connection. Datareader SourceRegards, Dinesh

Thanks. I'm going to give that a try now. I'll let you know the outcome. I can't imagine it being any worse than 90 minutes for 5 rows of data.

I think my problem is solved. On the first successful run, switching from OLE DB Source to ADO NET Source increased time from 95 minutes to 8 minutes. I've gotta try it a couple more times before I believe it :)