locked
Cannot connect to SQL 2008 when using Visual Studio / SSDT RRS feed

  • Question

  • I am working on a SQL 2016 server and trying to create an SSIS package to pull data from a SQL 2008 server.
    When I debug the SSIS package I get:

    Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Login timeout expired".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "A network-related or instance-specific error has occurred 
    while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Named Pipes Provider: Could not open a connection to SQL 
    Server [5]. ".

    In Visual Studio when I open the connection manager of the package and click 'Test Connection' I get Test Connection Succeeded.

    I can go to the Data Flow component - OLE DB Source Editor and click 'Preview' and I get rows returned.  When I am in SSMS from the SQL 2016 server, I can connect to the 2008 server, see all the databases, and 
    read / write without a problem.  Only when I am in Visual Studio and I debug the SSIS package, do I get the connection errors above.
    Has anyone seen this issue before?

    Here is the software evironment on the SQL 2016 server:
    Windows Server 2012 R2
    SQL Server 2016 SP2 Developer Edition
    SSMS 2016 (13.0.16106.4)
    Visual Studio 2017 (15.7.3)
    SQL Server Data Tools (15.1.61804.210)

    Tuesday, June 12, 2018 6:22 PM

Answers

  • Hi Gnav71,

    Examine the log in timeout value and if needed adjust it up.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by Gnav71 Wednesday, June 13, 2018 5:01 PM
    Tuesday, June 12, 2018 7:15 PM

All replies

  • Have you set any expression for connection string of your OLEDB connection manager?

    Or is it static connection set at design time?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, June 12, 2018 6:45 PM
  • Hi Gnav71,

    Examine the log in timeout value and if needed adjust it up.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by Gnav71 Wednesday, June 13, 2018 5:01 PM
    Tuesday, June 12, 2018 7:15 PM
  • No expression was set for this connections string.
    Wednesday, June 13, 2018 5:03 PM
  • Thanks Arthur!  I spent a day trying to figure this out.  For the record the connect timeout and general timeout were set at 0.  Once I increased these values to 120 the package worked.
    Wednesday, June 13, 2018 5:05 PM