Connecting to Oracle Database using Sql Server Integration Services

by

I have had to do this on many occasions, unfortunately the Oracle website is not particularly helpful when trying to determine what you need.

You need to download a copy of
“ODTwithODAC1120320_32bit.exe” which can be found at:

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

The 64-bit version is of no use, the 32 bit drivers will work just fine. Install this on your development machine and on the SQL Server box if you want to run the packages from the agent.

You can then choose the provider “.Net Providers\OracleCLient Data Provider”.
The connection string will look some like this (I think 1521 is the default Oracle port):

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxxx)))

More Related Contents:

  1. Is there a combination of “LIKE” and “IN” in SQL?
  2. SQL – How to store and navigate hierarchies?
  3. Can a foreign key be NULL and/or duplicate?
  4. Multiplication aggregate operator in SQL
  5. Stored Procedure with optional “WHERE” parameters
  6. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  7. convert Excel Date Serial Number to Regular Date
  8. SSIS Package not wanting to fetch metadata of temporary table
  9. Automate Version number Retrieval from .Dtsx files
  10. Query a database based on result of query from another database
  11. SSIS Source Format Implicit Conversion for Datetime
  12. Deferrable Constraints in SQL Server
  13. Benefits Of Using SQL Ordinal Position Notation?
  14. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  15. How to Map Input and Output Columns dynamically in SSIS?
  16. Unordered results in SQL
  17. update columns values with column of another table based on condition [duplicate]
  18. How do I list all the columns in a table?
  19. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  20. Backup SQL Schema Only?
  21. Is there an Oracle equivalent to SQL Server’s OUTPUT INSERTED.*?
  22. SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, =
  23. Recursive query in SQL Server
  24. How to create table using select query in SQL Server?
  25. Alternatives to LIMIT and OFFSET for paging in Oracle [duplicate]
  26. Split String by delimiter position using oracle SQL
  27. How can I get the number of days between 2 dates in Oracle 11g? [duplicate]
  28. Can you have if-then-else logic in SQL? [duplicate]
  29. How to add ‘ON DELETE CASCADE’ in ALTER TABLE statement
  30. SQL – Call Stored Procedure for each record

Leave a Comment