none
can't connect to SQL Server 2017 Integration Service from SSMS 18.2 object explorer RRS feed

  • Question

  • SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 171S

    SQL server management edition:

    SQL Server Management Studio 15.0.18142.0
    Microsoft Analysis Services Client Tools 15.0.1389.0
    Microsoft Data Access Components (MDAC) 10.0.17134.1
    Microsoft MSXML 3.0 6.0 
    Microsoft Internet Explorer 9.11.17134.0
    Microsoft .NET Framework 4.0.30319.42000
    Operating System 10.0.17134

    I used the SSMS's object explorer =>connect =>integration services, it popped out window for connect to server, in the server_name, I entered my local server name ( I tested the full sql server instance name as well) where the SQL server is running. SSMS is at the same local machine. and then I got the error messages: 

    cannot connect to TheHostNAme

    Additional information:

       Failed to retrieve data for this request: (microsfot.sqlserver.management.sdk.sfc)

          Exception from HRESULT: 0x0C))160AC (Microsoft.sqlserver.DTSRuntimeWrap)

              Exception from HRESULT: 0xC00160AC (Microsoft.sqlserver.DTSRuntimeWrap)

    here is the copied message:

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to WPHQ-WS6XMRWT2.

    ------------------------------
    ADDITIONAL INFORMATION:

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

    ------------------------------

    Exception from HRESULT: 0xC00160AC (Microsoft.SqlServer.DTSRuntimeWrap)

    For help, click: http://go.microsoft.com/fwlink/?LinkId=506689

    ------------------------------

    Exception from HRESULT: 0xC00160AC (Microsoft.SqlServer.DTSRuntimeWrap)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    

    

    Sunday, September 22, 2019 12:12 AM

Answers

  • Hi Peterzhou2019,

    • Integration Services service was relevant for SSIS 2005 - 2008R2. It is kept just for backward compatibility.
    • Starting from SSIS 2012 onwards, the SSISDB Catalog is what you need.
      Currently, you need to connect to SQL Server Database Engine only.

    Please see below.



    Sunday, September 22, 2019 12:50 AM

All replies

  • Hi Peterzhou2019,

    • Integration Services service was relevant for SSIS 2005 - 2008R2. It is kept just for backward compatibility.
    • Starting from SSIS 2012 onwards, the SSISDB Catalog is what you need.
      Currently, you need to connect to SQL Server Database Engine only.

    Please see below.



    Sunday, September 22, 2019 12:50 AM
  • Hi Yitzhak,

    You were absolutely right. New SSIS should use catalog. However, for some reason, we need to keep legacy SSIS stuff working. I found that with SSMS 17.9 for the SQL server 2017, the old way still worked locally. it did not work with SSMS 16.x or 18.2 though.  Other than 17.9, no matter what I tried to configure from perspective of component service Dcom or firewall inbound rule, it did not work. 

    Thanks and regards,

    Peter

    Sunday, September 22, 2019 4:23 PM
  • Hi Peter,

    MS SQL Server 2008/2008R2 are end-of-life versions, and out of Microsoft support.

    If possible in your environment, it is better to convert old pre-2012 SSIS packages to a newer version.



    Sunday, September 22, 2019 4:44 PM