0

I am using Visual Studio to create an SSIS package to get data from an Oracle database to use in SQL Server. Having created the dtsx file on my local machine, I then need to copy this to my server so I can run it from a scheduled job.

Development environment: Windows 10 64-bit, VS 2019 with SSIS designer.

Target server: SQL Server 2019

What I've tried so far. I am trying to add a connection manager into the package, and have tried both ".Net Providers\OracleClient Data Provider" and ".Net Providers for OleDb\Microsoft OLE DB Provider for Oracle". The first of these tells me I need "Oracle client software version 8.1.7 or greater", and the second tells me I need Oracle client and networking components, which are part of the "Oracle Version 7.3.3 client software installation". I have tried to find Oracle client software on the Oracle site, but don't see anything with these sorts of version numbers. All I can find is "Oracle Client for Microsoft Tools 19c (64-bit)" and "Oracle Instant Client", both of which I have tried and neither of which worked - I just keep getting exactly the same error messages.

Can anyone tell me what I need to install on my development machine to be able to create the connection (and which connection type should I use), and do I just install the same software/driver on the SQL Server machine to be able to run the package when I deploy it?

EDIT: Specifically, I need to know how to get this to show up in my SSIS designer

ADO.NET Connection Manager options

Skippy
  • 1,595
  • 1
  • 9
  • 13
  • Is your SSIS 32-bit or 64-bit? The Oracle client must be the same! – Wernfried Domscheit Feb 23 '23 at 21:22
  • Have you tried this https://learn.microsoft.com/en-us/sql/integration-services/data-flow/oracle-connector?view=sql-server-ver15? – jambis Feb 24 '23 at 12:29
  • Hmm, interesting questions. @jambis - no, I didn't try this since there is a note in that article to say "SSIS projects for VS 2019 and VS 2022 do not support designing packages targeting SQL server 2019." – Skippy Feb 24 '23 at 12:47
  • @WernfriedDomscheit - how would I find out which version of SSIS I have? And how does my development instance of VS2019 know whether the server I'm going to run the package on is 32-bit or 64-bit? – Skippy Feb 24 '23 at 12:53
  • Use sigcheck: https://learn.microsoft.com/en-us/sysinternals/downloads/sigcheck – Wernfried Domscheit Feb 24 '23 at 15:12
  • @WernfriedDomscheit - So, I can establish that I have both 32-bit and 64-bit versions of DTExec.exe available on my SQL Server 2019 machine, under 'Program Files (x86)' and 'Program Files' respectively (both say version 15.0.4261.1). My development desktop machine has SQL Server 2008R2 (Express) - don't ask! I need this for supporting a legacy application. This has just one copy of DTExec.exe, under 'Program Files', which is reporting as 64-bit (version 10.50.4000.0). – Skippy Feb 25 '23 at 13:40
  • You can install both clients, see https://stackoverflow.com/questions/24104210/badimageformatexception-this-will-occur-when-running-in-64-bit-mode-with-the-32#24120100 – Wernfried Domscheit Feb 26 '23 at 06:56
  • @WernfriedDomscheit, thank you for your continued interest and assistance with my question. At the risk of sounding rude or ungrateful, is there any chance you could provide a more complete set of steps to achieve my aim? At the rate of one short message per day, this could take quite a while to resolve! Something along the lines of "Your dev machine needs to have x, y and z installed, these can be downloaded from a.com and b.com. Once you've got these, use this connection manager with these settings...". Many thanks. – Skippy Feb 26 '23 at 10:31
  • I never worked with SSIS, so ther4e is not much I can help. In general you can [connect to an Oracle database](http://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999) in several ways. Which driver/provider you use is mainly a matter of taste. Almost all drivers/providers require at least an [Oracle Instant client](https://www.oracle.com/database/technologies/instant-client.html). Note, the Basic Oracle Instant Client does not include the **Oracle** OleDB, ODP.NET provider or ODBC driver. – Wernfried Domscheit Feb 26 '23 at 13:05
  • Have a look at [64-bit Oracle Data Access Components (ODAC) Downloads](https://www.oracle.com/database/technologies/odac-downloads.html), [32-bit Oracle Data Access Components (ODAC) and NuGet Downloads](https://www.oracle.com/database/technologies/odac-nuget-downloads.html) or [https://www.oracle.com/database/technologies/net-downloads.html](https://www.oracle.com/database/technologies/net-downloads.html) Usually it does not matter which one you install, as long as match 32/64 bit. – Wernfried Domscheit Feb 26 '23 at 13:08
  • So frustrating! I tried going down the NuGet route, but in SSIS when I go into `Tools` | `Manage Nuget Packages for Solution` I get a message saying "Operation failed. No projects supported by NuGet in the solution." – Skippy Feb 27 '23 at 16:47

0 Answers0