Hi ,

I have made the connection with oracle data base from SSIS by using Oracle Source task. Connection has being established but, not able get the data. It is not showing any error , it is showing like running for long time.  I have tested with several tables. Do I need to change any setting from oracle side or in SSIS.

please advice me.

t

Hello Raja - Can you explain about the SSIS package in bit more detail ? When DFT executes, it shows the progress on how many records have been pulled form source to destination. Are you seeing any progress in that ?

Let me also advise you to consider using Attuntity connector for fast data exchange between Oracle & SQL Server. These connectors are available only for SQL Server Enterprise Edition

thanks for reply ,

for the DFT executes, it shows the progress is like it is running .I waited around 30 min to run. But, did not get single records

I as already using Attuntity connector. please advice me

OK, Can you enable Package Execution Logs to check the progress of the execution. You can use Text file to dump the package progress and paste the contents here to check further (You may have to wait for long time so be patient).

Data Flow Task hung could also happen due to memory issues on this machine, you may be having less amount of left on your machine to process this package and these messages are appeared in the package execution logs when you enable it.

Can you check how much memory is free/left on this box at the time of execution ?

I have enabled the log and execute the package the log shows

Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.

after the final step I waited around 30 min there is no massage.

thanks for reply

Hello Raja - You will have to wait until it either finishes successfully or crashes.

Few questions:

1. Are you using Query to fetch the records or is it a direct table that you are pulling ?

2. Did you also check whether there is any blocking at Oracle end while fetching this data ?

3. Did you check how much available memory you have on this machine. Please check Available Memory MBytes under Memory counter class in perfmon and post back the findings here.

Additionally, Check with known cases about Attunity connectors here:

http://www.attunity.com/forums/archive/index.php/f-248.html

I have used  is it a direct table and also tried with query to fetch the records.Table contains lot of records.

 For the testing purpose, I started minimizing the records with query below are the test cases

test1: only 6 records - successful

test2: 169 records - successful

test3: 15000 records - keep on running

How could we know there is any blocking at Oracle end while fetching this data?

 Could you please advice me, how to check how much memory is available ? Available Memory MBytes under Memory counter class in perfmon and post back the findings here.

thanks in advance

You can also check the Task Manager - Performance Tab: Here check for the values for Total, Cached, Available & Free

Total-4094

Cached- 1152

 Free- it changing from 4 to 16

Hello Raja -Did you try other options like Fast Load or Array Mode and by also tuning Transfer size, batch size etc, as described in these Attunity forums:

http://www.attunity.com/forums/microsoft-ssis-oracle-connector/performance-microsoft-attunity-connector-oracle-2067.html

http://www.attunity.com/forums/microsoft-ssis-oracle-connector/increase-performance-attunity-connectors-1596.html

thanks a lot this was helpful.

I am able to load the data now, I have one more question.

I want to load the data into the SQL from oracle. Kind of building the data warehouse.

the requirement is every week I want to run the SSIS package through SQL agent  for each run I want to append fresh week data. I do not want to do look-up or upsert operation since, it is not the well performance solution. 

For the 1st run, I have loaded 28 weeks of data at a time from now on I want to append week-29 for next Monday, after that next run week-30. Is there any possibility to retrieve latest week data from oracle?, then we can append to sql

thanks,   

Hello Raja - For this, the best way would be to write & use custom written query. I guess as of now you are using Table Load option which needs to be converted into Query and use expression in the where clause to fetch relevant records.

Hope this helps