SSIS performance issue when using IP addresses in connection strings

• dwall 30994

  SSC Enthusiast

  Points: 179

  More actions

  May 18, 2015 at 8:29 am

  #308143

  Hi,

  We've having an "interesting" issue with connection strings in SSIS. Running a relatively simple ETL package, using an IP address in the connection string to transfer data between databases on a SQL server works without any issues (around 1.2 seconds). If we try to run the package on the server itself, it takes a little over 40 seconds. The package is taking 5 seconds at the start of each step (after the validation has completed), meaning that the package (with 8 steps in it) is taking over 40 seconds to run. If we change the connection string to use the name of the server rather than the IP/port, then the package executes in the expected 1 second.

  Anyone any ideas?

  Thanks in advance

• Martin Schoombee

  SSCoach

  Points: 19134

  More actions

  May 19, 2015 at 10:55 am

  #1799105

  Only thing that comes to mind is to maybe do a tracert on both the IP and dns name, to see if for some odd reason the traffic is routed differently when using the IP.

• Nevyn

  SSChampion

  Points: 14210

  More actions

  May 19, 2015 at 11:49 am

  #1799121

  Yeah this sounds like more of a network set up issue than an SSIS issue.

  Is there a reason you want to use the IP though? Its like the old doctor joke "it hurts when I do this". "Ok, don't do that".

• dwall 30994

  SSC Enthusiast

  Points: 179

  More actions

  May 20, 2015 at 8:42 am

  #1799342

  Thanks for the thoughts - we'd checked routing and they were identical. it was suggested the TCP Chimney offloading might be a cause, but I couldn't replicate issue.

  We'll go with the name route (we don't always have name resolution on the servers we look after - hence the use of the IP/port)

  Thanks anyway