0

I am trying to connect oracle server by C# in SSIS script task and run some select/insert queries there. But even I added Oracle.DataAccess.dll, it is still failing because of assembly issue. BTW, Execute sql task can connect to oracle server with same connection string. Here is code that i use for connect:

class Ora
{
    OracleConnection con;
    public void Connect()
    {
        con = new OracleConnection();
        con.ConnectionString = "Data Source=ServerName;User ID=UserID;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Password=pass";
        con.Open();
        Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    }

    public void Close()
    {
        con.Close();
        con.Dispose();
    }

}
public void Main()
    {
        // TODO: Add your code here
        Ora ot = new Ora();
        ot.Connect();
        ot.Close();
        Dts.TaskResult = (int)ScriptResults.Success;
    }

And I am getting below error:

An exception of type 'System.IO.FileNotFoundException' occurred in ST_c321d7d567aa46c7bad48f7f0d92bed5 but was not handled in user code

Additional information: Could not load file or assembly 'Oracle.DataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.

Community
  • 1
  • 1
Can Aslan
  • 53
  • 2
  • 11
  • Did you install x64 **and** 32 bit flavors of Oracle.DataAccess? Maybe try changing your ScriptTask to prefer 32 bit. – Filburt May 17 '17 at 06:46
  • There are 4.121.2.0 and 2.121.2.0 versions installed on server. I think they have both. – Can Aslan May 17 '17 at 06:54
  • Try creating database link on your machine as [described here](http://stackoverflow.com/a/10480011/205233) and try to run it both from a x64 and a 32 bit cmd window to see if both OraOleDb drivers work. If you have SSMS, you can check if OraOleDb is listed as a driver fro linked servers. This could also be a workaround: If else fails, create a Linked Server connection to your Oracle db and use it from a Sql OleDb connection. – Filburt May 17 '17 at 07:10
  • @Filburt, i will try but before it just letting you know. I can connect by execute sql task and Oracle Source/Destination tasks in dataflow, and yes there is oracle drive. – Can Aslan May 17 '17 at 07:23

0 Answers0