OLE DB Destination PacketSize: package running slower with increased packet size??
I have a fairly simple package reading data from a flat file source (approx 1 million rows) and writing into a SQL Server destination. I'm connecting to the destination table through SSIS's ole db. All items (package,source file, destination server) are on the same physical machine. I noticed something strange though, while running tests on changing different properties: With the PacketSize property on the OLE DB dest at the default value of 0 (assuming this will transfer data over the network in the default 4KB chunks) the transfer takes 45 seconds. I changed the PacketSize to the maximum of 32,767 (32KB), now the transfer takes almost 2 1/2 minutes! I expected that the increased packet size would always result in a faster transfer - or if not faster, i.e. if reading from the flat file source is the weakest link in the chain -- hardly any transformations happening -- then the total time taken would stay the same. How is it that increasing the network packet size actually results in a much slower package?
October 7th, 2011 7:06am

If all is on the same box, shared memory should be used, what is your conn string? Shared memory WILL be used if you specify in your OLEDB conn server as (local) or LocalHost. Try this setting out. As an aside, in theory, increasing the packet size when moving large data chunks across the network should speed the process up. Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 7th, 2011 10:02am

If all is on the same box, shared memory should be used, what is your conn string? Shared memory WILL be used if you specify in your OLEDB conn server as (local) or LocalHost. Try this setting out. As an aside, in theory, increasing the packet size when moving large data chunks across the network should speed the process up. Arthur My Blog
October 7th, 2011 5:00pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics