Search code examples
oracle-databasessisdatabase-connectiontnsnames

SSIS connection to Oracle

First of all I have to say that I am kind of new with this aspect of SSIS. I am trying to create a connection with a Oracle database from a package in SSIS. The thing is, this project was done initially in Access, it was my job to translate it into SSIS, the machine where the project is located is a 32 bit pc, it has MS Visual Studio 2008 working on 32 bits. The pc where I am working is a 64 bits machine, with MS VS 2010.

I began to look for information useful to try to solve this problem. Someone told me that it was necessary to copy the tnsnames.ora files from the first pc to mine, and copy part of the code where I was pointing to the database in all of the .ora files present in the hard drive. In this order of ideas I added the part of the code corresponding to the database:

DBNAME.WORLD =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = name)(PORT = ####))
      (ADDRESS = (PROTOCOL = TCP)(HOST = name)(PORT = ####))
    )
    (CONNECT_DATA =
      (SID = VAPP)
    )   )

I found these files in the next directories:

Dev6 - oracle - ORADEV6i - ORANT

So, I did it but it was useless I always get the same result:

Test connection failed because of an error in initializing provider

Then I found that it was also necessary to modify the "path" variable to specify the new directory for Oracle, but at this point I do not know which of the directories above is the right one. As you can see I am very confused with this issue, I have been trying to be as clear as possible with my doubt. I hope you can understand, given that English is not my mother tongue.

Solution

  • There is a ton of information on the web about connecting to Oracle with SSIS. That is because it is total voodoo.

    I suggest using the attunity adapter:

    This is the 2008 version: http://www.microsoft.com/en-us/download/details.aspx?id=29284 This is the 2012 version: http://www.microsoft.com/en-us/download/details.aspx?id=29283

    This is an excellent resource on how to make attunity work and all the steps you need: http://technet.microsoft.com/en-us/library/ee470675%28v=sql.100%29.aspx

    Note that you will need to install the oracle drivers in order to make this work. You can download those for free.