locked
ado.net task using an oledb driver, is that ok? RRS feed

  • Question

  • Hi,  In my company a central body sets our software at vs 2017 enterprise with ssdt 15.x .   we are not allowed to download our own drivers and just get what they give us for oracle and db2.   when we went to vs 2017 I noticed that for oracle and db2 the only drivers that would work for an ado.net task were oledb drivers.   the ado.net driver for oracle would not work with with the ado.net task in a new ssis package.   When you "test connection" it gave an error.   But if I pointed the ado.net task for oracle at the oledb driver, it worked.   Just for various reasons we don't use an oledb task with an oledb driver, although I could argue for that going forward.   Is there anything wrong with using an oledb driver with an ado.net task?   Like could I have a data type problem in a 1 in 1000 situation or some other compatibility problem?  It seems like they are two completely different protocols.
    Tuesday, August 4, 2020 4:57 PM

All replies

  • Hi etlman,

    On the surface, it seems like a kludge.

    What is your SQL Server/SSIS version on a run-time server?



    Tuesday, August 4, 2020 5:13 PM
  • Yitzhak,

          I agree.  I was surprised it even worked.   I could be wrong about db2.   I think with db2, the ado.net driver worked but people in my group wanted to use the ado.net (almost all our stuff is ado.net task) task with the oledb driver.   And what about performance issues by crossing up the task and the driver?

          In terms of the run time environment it's Azure, I don't have a lot of visibility there, I think the SSIS is ssdt 15.x.   They told us to get off of vs 2015 built packages on the server a while ago.   I can see the drivers (oracle, teradata) on azure there but not the ssis versions I think.  

         i'm thinking i haven't tried to run the cross protocol approach for oracle on azure.   i'll try that soon.

         



    • Edited by etlman Tuesday, August 4, 2020 5:48 PM
    Tuesday, August 4, 2020 5:38 PM
  • Hi etlman,

    You can check in VS SSIS project what SSIS version is targeted on the server.

    Please see below.

    Tuesday, August 4, 2020 5:50 PM
  • Yitzhak,  the TargetServerVersion is SQL Server 2017.

    i ran it on azure with an ado.net task and an oledb oracle driver and it worked.   i even went into the package with notepad and you could see the oledb driver in the oracle connection string.  small job that wouldn't reveal any performance problems, but it ran clean.


    Tuesday, August 4, 2020 6:17 PM
  • Hi etlman,

    We can choose one of the .Net Providers and .Net Providers for OleDb according to your need.

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 5, 2020 9:55 AM
  • Mona,

          We were never able to get "Microsoft OLE DB Provider for Oracle" to work for anything.   Regardless of this Ado.Net/OLEDB Task question or on VS 2015 Enterprise we couldn't get the Microsoft driver to work for Oracle.   We had to use the "Oracle Provider for OLE DB" that came with the Oracle client install to get anything to work.  If you pick the Microsoft Oracle driver and you haven't installed the Oracle client I don't think that would work anyway.   I was able to get c# (not ssis) with just a "Oracle Developer Tools for Visual Studio 12.2" install to access Oracle.  I had to nuget Oracle.ManagedDataAccess.Core but I was surprised that it worked without the Oracle client install.   Later I installed the Oracle client.



    • Edited by etlman Wednesday, August 5, 2020 3:22 PM
    Wednesday, August 5, 2020 3:10 PM
  • Hi, an update.  I decided to give "Microsoft OLE DB Provider for Oracle" another chance now that I had all my oracle drivers installed.   Kind of shocking but opening an OLEDB task and asking for "Microsoft OLE DB Provider for Oracle" completely abends Visual Studio.  It is just picking that driver that kills it, I didn't even put in any connection information (i didn't have a chance, the gui died)  I tried it twice and it killed VS 2017 twice.  I'm Visual Studio Enterprise 2017 V 15.9.4, sql server data tools V 15.1.61810.11040, ssis V 15.0.1100.123 .
    Wednesday, August 5, 2020 10:21 PM
  • Hi etlman,

    Please choose OracleClient Data Provider in the connection manager.

    Please refer to .NET Framework Data Provider for Oracle.

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 7:01 AM