none
Unable to create an Excel Connection Manager not recognized as a valid connection type. RRS feed

  • Question

  • Suddenly, after the most recent Microsoft update (day before yesterday), SSIS no longer recognizes Excel as a valid connection manager type. The SSIS packages have been working well for over a year.   Here is the error message

    TITLE: Microsoft Visual Studio
    ------------------------------

    The new connection manager could not be created.

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

    The connection type "EXCEL, {8FD37A45-01A4-210C-6C6D-575139717076}" specified for connection manager "{F118BC96-4456-4B60-A69F-1E69A7BACCFF}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

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

    I tried creating a new Excel connection manager, and that didn't work either.

    I'm running SQL Server 2017 and the most current version of SSDT (just downloaded and installed as an attempt to fix this issue).  The machine is 64-bit Windows 2012 R2.

    I'm running Microsoft Office 365, but again, things have been running perfectly until today with SSIS accessing Excel.

    My guess is that something, perhaps in yesterday's Windows update corrupted, whatever it is that SSDT needs to connect to an Excel data source, but that's only a guess.

    Any possible solution?  Is there something I need to reinstall?  Please advise.


    • Edited by Karen Grube Friday, January 17, 2020 5:49 AM
    Friday, January 17, 2020 5:47 AM

Answers

  • Finally, yes.

    It took uninstalling and reinstalling SSDT 2017 from the standalone installer, uninstalling all versions of the Access database engine and reinstalling the Microsoft Access database engine 2016 - 32 bit.  It was quite a process.  But it was finally successful.

    Feel free to ask for more details, but that was basically it. 

    Monday, January 27, 2020 3:55 AM

All replies

  • Hi Karen Grube,

    Please recreate the Excel connection manager and refresh the Excel source.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 17, 2020 8:30 AM
  • I did try that.  I deleted the original Excel Connection Manager then right-mouse clicked in the connection manager area of SSIS.  Then I tried to create a new connection, selecting Excel as the connection type, but the same error message displayed saying that Excel is not recognized as a valid connection manager.

    Someone mentioned something in an article about setting the debug 64 bit option to false, but I don't know where to do that within SSIS.  Do you?I looked in Debug Options and couldn't find it.

    Any other suggestions?

    Friday, January 17, 2020 8:48 AM
  • Hi Karen Grube,

    Please right click the Integration Services Project and choose the properties.

    In debugging, we can set Run64BitRuntime as False.

    Please see the following pictures:

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 17, 2020 9:01 AM
  • No, that didn't help.

    I tried another experiment and it also failed with the same error:

    In SQL Server, in one of my databases, I right-mouse-clicked "Tasks" then "Import Data."  At that point, I tried selecting Excel from the drop-down, and got the same error that I see within SSIS: Excel isn't recognized as a valid connection type.

    Any other ideas?  My guess is that I have to reinstall something, but i'm not sure what: drivers?  

    I'll connect with the Microsoft Office team to see if they have any suggestions and I'll let you know, but if you have anything else I can try, please let me know.

    Friday, January 17, 2020 5:28 PM
  • Hi Karen,

    SSIS Excel connection manager is using internally Microsoft ACE OLEDB Provider. 

    There are three existing versions of it:

    • 12.0
    • 15.0
    • 16.0

    Please check what version of the Microsoft ACE OLEDB Provider is installed on the machine, and what version is referred to in the SSIS Excel connection.

    Friday, January 17, 2020 7:54 PM
  • The Connection Manager was removed and I can't create a new one.  Neither SSIS nor SQL will allow me to even get that far.  How else can I check for the version of the Microsoft ACE OLEDB Provider I'm running?
    Friday, January 17, 2020 8:16 PM
  • Hi Karen,

    You can do it in SSMS.

    Please  issue the following command there:

    -- 0. Check what 64-bit OLEDB providers are installed
    EXEC master.sys.sp_MSset_oledb_prop;


    Output:

    P.S. You can find me on the LinkedIn and Skype.

    Friday, January 17, 2020 8:23 PM
  • I see these:

    SQLOLEDB
    SQLNCLI11
    Microsoft.ACE.OLEDB.16.0
    ADsDSOObject
    MSOLEDBSQL
    SSISOLEDB
    Search.CollatorDSO
    MSDASQL
    MSOLAP
    MSDAOSP

    It looks as though the ACE.OLEDB.12.0 is missing . . . 

    I'll see about downloading it.



    • Edited by Karen Grube Saturday, January 18, 2020 12:32 AM
    Saturday, January 18, 2020 12:10 AM
  • I downloaded the Microsoft Access 2010 Redistributable, but nothing new showed in the list.

    What is the next step after installing.  I'm still getting the same error.  I'll try restarting SQL.



    • Edited by Karen Grube Saturday, January 18, 2020 12:34 AM
    Saturday, January 18, 2020 12:31 AM
  • Restarting SQL server didn't help.  I'm not sure what to do next.
    • Edited by Karen Grube Saturday, January 18, 2020 12:41 AM
    Saturday, January 18, 2020 12:37 AM
  • I tried installing the Microsoft 2013 Access Redistributable, but it won't install because I'm running the Microsoft Access 2010 Redistributable, which is 32-bit.  HELP!
    Saturday, January 18, 2020 12:43 AM
  • I installed Microsoft Access Redistributable 2010 for 64-bit, then Microsoft Access Redistributable for 2013 for 64-bit..

    Now, the list includes:

    Microsoft.ACE.OLEDB.12.0

    Microsoft.ACE.OLEDB.15.0

    Microsoft.ACE.OLEDB.16.0

    And I still can't create a connection to an Excel file.  HELP!  I'm going to try rebooting the actual server now to see if that makes a difference.

    Saturday, January 18, 2020 12:57 AM
  • I have also now repaired SQL Server 2017, and that did not resolve the issue.  YEASH!
    Saturday, January 18, 2020 9:13 PM
  • Any other possibilities?  Please let me know.
    Saturday, January 18, 2020 10:49 PM
  • We're working on reinstalling the 32-bit version of Office 365 to see if that helps.
    Sunday, January 19, 2020 1:53 AM
  • Hi Karen Grube,

    May I know if you have anything to update?

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 27, 2020 1:54 AM
  • Finally, yes.

    It took uninstalling and reinstalling SSDT 2017 from the standalone installer, uninstalling all versions of the Access database engine and reinstalling the Microsoft Access database engine 2016 - 32 bit.  It was quite a process.  But it was finally successful.

    Feel free to ask for more details, but that was basically it. 

    Monday, January 27, 2020 3:55 AM
  • Hi Karen Grube,

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Saturday, February 1, 2020 2:59 AM