I am trying to load data from sql destination to oracle destination.SQl DB is on local machine. Oracle DB is on remote machine. I am trying to connect both source and destination using MS OLEDb provider of SSIS.However it takes very long time to process and load 10000 row batch. The oracle table has no indexes and no constraints. I have tried doing the reverse process and it is very fast.This rules out network connectivity issues.But when i want to transfer into oracle the package just runs and runs.I dont think attunity provider for oracle will tremendously enhance the performance. what can be the possible reason. All my source and destination data types are string and char? Tried the scenario with ADO.NET provider.Still the behavior was not different. Any help is higly appreciated Viswa

did you used OLEDB Source and Destination? did you set access mode in destination as Table or View Fast Load?( this will raise up speed)http://www.rad.pasfu.com

yes both the source and destinations are oleDB providers.The fast load options are available when i chose sql server source or destination.but when i choose oracle ole db destination..those options are not available

yes both the source and destinations are oleDB providers.The fast load options are available when i chose sql server source or destination.but when i choose oracle ole db destination..those options are not available

You did not provide details on what your batch involves. I would try to figure out where the bottleneck is. Do you have an Oracle DBA available, I would ask to profile how fast the IO occurs on the target side. If not, query the Oracle side at the same time the package executes to see how fast it accepts the records.Arthur My Blog

You did not provide details on what your batch involves. I would try to figure out where the bottleneck is. Do you have an Oracle DBA available, I would ask to profile how fast the IO occurs on the target side. If not, query the Oracle side at the same time the package executes to see how fast it accepts the records.Arthur My Blog

i have set my defaultMax rows as 10,000..i do not have acess to client server where oracle DB resides.Engine threads being used are 10 and default buffer size is of 10 MB.(Default value). I think something is wrong on the oracle side when doing a write operation in DB.as reading data from oracle and loading in sql is very fast(reverse process).