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