none
what is the latest thinking on oledb, odbc, ado.net and native connectors? RRS feed

  • Question

  • Hi I'm running ssis 2017.  There was always debate and confusion on the relevancy , speed etc of these connector technologies in the context of ssis.  

    Last I heard ODBC is faster than oledb in rdms's that support both.  Is that true?  and oledb migh be on its way out.  and not all technologies including file system even support oledb.

    Its never been clear to me where ado.net and native come into the picture.  Perhaps ado.net is especially useful for scripts and maybe ssas tasks?

    is there an accurate comparison and explanation of these things anywhere?  perhaps written in the last year or so?

    Wednesday, June 12, 2019 4:59 PM

Answers

  • Hi there,

    I would say the speed of a driver is not as impactful as developers knowing what they are doing and how they design the data flow to be effecient.

    Reading from disk should not even come into the picture because it will be slow no matter what.

    Overall, the Data World is gearing toward the use of streaming data so the drivers themselves would become obsolete.

    I think what it comes to SSIS, the Microsoft's current approach is to support them all.

    ODBC is often the only way to connect to many storage systems.
    OLEDB is an automated bridge that is nice to have as a universal tool and ADO would remain the go to connector for any Microsoft stack especially Azure so you will see more need in it. E.g. try accessing the protected storage in Azure - it is for a trivial database use.

    Lastly, any public documents should be searchable online.


    Arthur

    MyBlog


    Twitter

    Wednesday, June 12, 2019 5:07 PM
    Moderator
  • Streaming Data is not based on sets, rather RPC protocols.

    What I mean above is if you need to access the Azure Key Vault then AFAIK ADO is the only option.

    So the life is not going to change in respect to the connectivity choices.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by db042190 Thursday, June 13, 2019 9:34 PM
    Wednesday, June 12, 2019 8:18 PM
    Moderator
  • Hi db042190,

    The following links will be helpful.

    SSIS Data Flows – ADO.NET vs. OLE DB vs. ODBC

    Integration Services (SSIS) Connections

    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

    • Marked as answer by db042190 Thursday, June 13, 2019 9:33 PM
    Thursday, June 13, 2019 5:33 AM
  • ODBC is old and is low-level (technical). Microsoft definitely tried to drop ODBC.

    ??? ODBC is an industrial Standard and it's not planned to drop ODBC. OleDB was deprecated and MS suggest to use ODBC instead

    https://www.dbdelta.com/deprecated-sql-server-data-access-technologies/

    ADO .Net is the newest Technology.

    Yes, but SSIS is written in unmanaged code and using here a managed code data Provider is ineffiecent.

    You should use EF if possible.

    In own application, yes; but we are talking about SSIS and here is Entity Framework useless.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by db042190 Thursday, June 13, 2019 9:35 PM
    Thursday, June 13, 2019 6:05 AM

All replies

  • Hi there,

    I would say the speed of a driver is not as impactful as developers knowing what they are doing and how they design the data flow to be effecient.

    Reading from disk should not even come into the picture because it will be slow no matter what.

    Overall, the Data World is gearing toward the use of streaming data so the drivers themselves would become obsolete.

    I think what it comes to SSIS, the Microsoft's current approach is to support them all.

    ODBC is often the only way to connect to many storage systems.
    OLEDB is an automated bridge that is nice to have as a universal tool and ADO would remain the go to connector for any Microsoft stack especially Azure so you will see more need in it. E.g. try accessing the protected storage in Azure - it is for a trivial database use.

    Lastly, any public documents should be searchable online.


    Arthur

    MyBlog


    Twitter

    Wednesday, June 12, 2019 5:07 PM
    Moderator
  • thx Arthur what did you mean by ...

    E.g. try accessing the protected storage in Azure - it is for a trivial database use.

    ...especially the part after the dash.  what is trivial db use and isn't protected storage in azure file system rather than rdbms?

    Wednesday, June 12, 2019 5:21 PM
  • also wouldn't we still need ado.net or native for streaming data?  something has to talk to the databases.
    Wednesday, June 12, 2019 6:05 PM
  • ODBC is old and is low-level (technical). Microsoft definitely tried to drop ODBC.

    OLE/DB is defined to be efficient and also low-level (technical).

    ADO .Net is the newest technology.

    Programmers often emphasize machine efficiency but ignore people efficiency. People efficiency is important; management might consider it to be most important.

    The latest technology is Entity Framework. It (generally) provides the most people efficiency and Microsoft also makes it machine efficient. You should use EF if possible.



    Sam Hobbs
    SimpleSamples.Info

    Wednesday, June 12, 2019 7:44 PM
  • Streaming Data is not based on sets, rather RPC protocols.

    What I mean above is if you need to access the Azure Key Vault then AFAIK ADO is the only option.

    So the life is not going to change in respect to the connectivity choices.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by db042190 Thursday, June 13, 2019 9:34 PM
    Wednesday, June 12, 2019 8:18 PM
    Moderator
  • Hi db042190,

    The following links will be helpful.

    SSIS Data Flows – ADO.NET vs. OLE DB vs. ODBC

    Integration Services (SSIS) Connections

    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

    • Marked as answer by db042190 Thursday, June 13, 2019 9:33 PM
    Thursday, June 13, 2019 5:33 AM
  • ODBC is old and is low-level (technical). Microsoft definitely tried to drop ODBC.

    ??? ODBC is an industrial Standard and it's not planned to drop ODBC. OleDB was deprecated and MS suggest to use ODBC instead

    https://www.dbdelta.com/deprecated-sql-server-data-access-technologies/

    ADO .Net is the newest Technology.

    Yes, but SSIS is written in unmanaged code and using here a managed code data Provider is ineffiecent.

    You should use EF if possible.

    In own application, yes; but we are talking about SSIS and here is Entity Framework useless.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by db042190 Thursday, June 13, 2019 9:35 PM
    Thursday, June 13, 2019 6:05 AM
  • ODBC is old and is low-level (technical). Microsoft definitely tried to drop ODBC.

    ??? ODBC is an industrial Standard and it's not planned to drop ODBC. OleDB was deprecated and MS suggest to use ODBC instead

    https://www.dbdelta.com/deprecated-sql-server-data-access-technologies/

    What standard? Is it an ISO standard or an ECMA standard or something else? Or does standard just mean that it is commonly used?

    It is a fact that ODBC is the original, the oldest of all mentioned here. And it is low-level. I cannot find anything supporting my statement that Microsoft tried to drop ODBC but I know it happened. People protested, so Microsoft kept ODBC, just as they are keeping OLE/DB. See Announcing the new release of OLE DB Driver for SQL Server – Microsoft SQLNCli team blog.

    ADO .Net is the newest Technology.

    Yes, but SSIS is written in unmanaged code and using here a managed code data Provider is ineffiecent.

    Sorry, I did not realize this is SSIS. However I am sure Microsoft would disagree about mixing managed and unmanaged code.



    Sam Hobbs
    SimpleSamples.Info

    Friday, June 14, 2019 7:31 AM
  • https://en.wikipedia.org/wiki/Open_Database_Connectivity


    That is not a standard and you can see in the talk page that I said the article does not specify a standard. The Wikipedia is not authoritive; they require an authority for the information in the articles. I see nothing in the Wikipedia article providing an authority for the statement that ODBC is a standard; if you have that information then you should update the article.


    Sam Hobbs
    SimpleSamples.Info

    Friday, June 14, 2019 8:15 AM