.net application's oracle connection timing out

Question

i have the code below trying to do a bulk copy from oracle to SQL server 2005 and it keeps timing out. how can i extend the oracle connection timeout? it seems i can not from what i read on the web.

OracleConnection source = new OracleConnection(GetOracleConnectionString());
source.Open();

SqlConnection dest = new SqlConnection(GetSQLConnectionString() );
dest.Open();


OracleCommand sourceCommand = new OracleCommand(@"select * from table");


using (OracleDataReader dr = sourceCommand.ExecuteReader())
            {
                using (SqlBulkCopy s = new SqlBulkCopy(dest))
                {
                    s.DestinationTableName = "Defects";
                    s.NotifyAfter = 100;
                    s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);
                    s.WriteToServer(dr);
                    s.Close();
                }
            }

source.Close();
dest.Close();  

here is my oracle connection string:

return "User Id=USER;Password=pass;Data Source=(DESCRIPTION=" +
                    "(ADDRESS=(PROTOCOL=TCP)(HOST=14.12.7.2)(PORT=1139))" +
                    "(CONNECT_DATA=(SID=QCTRP1)));";

Solution 1

In your connection string, there is a 'Connection Lifetime' and 'Connection Timeout' parameter. You can set it accordingly. See here for the full reference.

BTW, I know you didn't ask this, but have you considered an ETL tool for migrating your DB records (e.g. Informatica, FME, etc.)? While your approach is valid, it isn't going to be very performant since you are hydrating all of the records from one DB to the client and then serializing them to another DB. For small bulk sets, this isn't a big issue, but if you were processing hundreds of thousands of rows, you might want to consider an official ETL tool.

Solution 2

You can set s.BulkCopyTimeout option

Comments

[kacalapy]

its not in the budget, and there are only 20,000 records. i want to be a good citizen and not lock resources more than required

[kacalapy]

is there a free ETL tool i can use to get ORACLE data into SQL, and schedule this to run periodically each day?

[Jordan Parmer]

I don't have any recommendations off the top of my head, but Googling "free etl" returned some promising results. Most ETL tools are database agnostic (or at least support Oracle and SQL Server).

[kacalapy]

the link doesn't seem to help: i am using the "Without TSN.ora alternative" as you can see by the connection string and i dont see a param there for timeout or lifespan

[Jordan Parmer]

That is okay. The fact you are using a direct connect doesn't mean you can't add the parameter. Just add the parameters after your last semi-colon in the connection string. eg. "...(SID=QCTRP1)));Connection Timeout=300;"

[kacalapy]

setting Connection Timeout=6000; did not help.