none
SSIS 2016 - Visual Studio 2015 - Excel Hanging due to proxy RRS feed

  • Question

  • Fellow SQLers,

    The problem I am about to described just started happening about 3 days ago - no changes to the VM.

    This uat sever is a VM.

    it houses SSIS 2016 and SSDT 2015 with VS 2015 Update 3.

    Our SSIS package connects to an Excel 2013 xlsx file (on that VM) with the 2007-2013 properties in the connection manager. When you click the drop down to pick the sheet, it can hang from 30 seconds to minutes. this VM was not internet unabled and it always had worked.

    We noticed that by turning on the proxy to the internet. All of the problems went away. We also uncovered in the logs that when we were in SSDT 2015, we could see the operation was trying to get to  the below - with the proxy on it was fine. Of course with the proxy off, it failed.

    TCP_MISS/200 5769 CONNECT dc.services.visualstudio.com:443 - DIRECT/40.114.241.141 -
    TCP_MISS/503 0 CONNECT sqm.telemetry.microsoft.com:443 - DIRECT/65.55.252.93 -

    Is there a way to turn these transfers off?

    Thanks

    MG

    Thursday, October 3, 2019 3:32 PM

Answers

  • HI Art,

    thanks for the info.

    Excel is not accessed remotely. It is on the same VM that ssis is on, everything we need is on the VM.

    FYI, we resolved it with a firewall change.

    MG

    • Marked as answer by mg101 Friday, October 11, 2019 3:03 PM
    Friday, October 11, 2019 3:03 PM

All replies

  • Hi mg101,

    It is Microsoft telemetry. Check it out here: Application Insights: Frequently Asked Questions

    So you can find the ApplicationInsights.config file on the server and modify the URL paths to point to the machine loopback IP address/localhost 127.0.0.1 to fake it.

    • Edited by Yitzhak Khabinsky Thursday, October 3, 2019 4:20 PM
    • Marked as answer by mg101 Thursday, October 3, 2019 4:49 PM
    • Unmarked as answer by mg101 Friday, October 11, 2019 12:16 PM
    Thursday, October 3, 2019 3:55 PM
  • thanks Yitzhak

    MG

    Thursday, October 3, 2019 4:49 PM
  • Yitzhak,

    We cannot find the file mentioned. We are using VSDT not the full version of Visual Studio.

    any suggestions?

    thanks,
    MG

    Friday, October 11, 2019 12:16 PM
  • Hi mg101,

    I would change the design so Excel is not being accessed remotely.

    You can copy it to a local server instead. Or make the application that drops it rather deposit it to the SSIS box.


    Arthur

    MyBlog


    Twitter

    Friday, October 11, 2019 1:14 PM
    Moderator
  • HI Art,

    thanks for the info.

    Excel is not accessed remotely. It is on the same VM that ssis is on, everything we need is on the VM.

    FYI, we resolved it with a firewall change.

    MG

    • Marked as answer by mg101 Friday, October 11, 2019 3:03 PM
    Friday, October 11, 2019 3:03 PM