Hi all i am doing a simple dataflow task, the sourtce is an Oracle database and the target is a sql server table. I have a query with a WHERE clause which when run against the source databse gives me 25000 records, now when i execute the package its taking forever, please can anyone tell me where should i look at and what should i look for? Thanks

If the DFT remains yellow - means keeps executing, this is because the native Oracle connector is so slow - it is a row by row ops. You may want to go with a different connector, the best is from AttunityArthur My Blog

Thanks Arthur unfortunately i cannot download or purchase any 3rd party tool is there any other way in SSIS?

Does the package ever complete? If you are on the Enterprise version of SQL Server then the Attunity connector is free.Arthur My Blog

I waited for an hour and a half and stopped the debugging. No we dont have the enterprise edition. Thanks

If it does not complete then there is a bottleneck or even hidden errors somewhere. In your case it looks more like there is an issue. Review your setup against this post: http://blogs.msdn.com/b/sqlperf/archive/2009/08/14/using-ssis-to-get-data-out-of-oracle-a-big-surprise.aspxArthur My Blog

Thanks Arthur but that didnt help me out , please can any one tell me why my simple data transfer job is taking forever, please need help. Its a simple job of extracting data from Oracle db and loading it into sql server db.

Because it run (if it works) typically on a row by row basis, and this: do you see any warnings in the package progress?Arthur My Blog

If thats the case of row by row, how come when i run the query from SQL*Plus it takes only few seconds? Thanks

SQL*Plus is a remote console to the Oracle DB engine, there you run your code on the Oracle server directly, with SSIS it is a different story as your query needs to cross the platform boundaries as the data type mapping from a provider to provider (drivers) and network. So it is like comparing Oranges to Apples.Arthur My Blog

Thanks Arthur Other than Attunity is there anyother way or where can i look for the details as to whats taking so long for the task to run i mean where can i get the details that shows the reson where the task is stalling?

Very little can be done to help you see where the problem is because the execution particulars are hidden. But there is a chance you have mis-configured something. The most typical cause it running raw SQL statements from SQL, you instead can create an Oracle package and call from it, this will constitute a remote call and this will run faster. Can you post the screenshots of your OELDB settigns?Arthur My Blog

Thanks Arthur i'm new to SSIS and by OELDB Settings, did you mean the connections of the cource? and when you said i need to create an Oracle Package how should i create and where should i create? Thanks

Just provide the advanced editor config of the OLEDB destination. To create an Oracle package you need to use Oracle tools as Pl*SQL or Oracle Developer, Toad, etc. You may find info here: http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_6006.htmArthur My Blog

how can i paste the screen shot or is there a way where i can attach a doc?

Use this button ^ or you can use www.SkyDrive.com or www.ImageShack.comArthur My Blog

Can i have your email id and i can send it as attachment? Thanks

I prefer people posting all the relevant info into the forum thread because it may serve others in form of a more complete reference at a later time.Arthur My Blog

I understand but in my case the websites you mentioned i cannot open them, else is there a way i can upload .doc , it is not that i dont want other people to benifit from my question, its just there is no other way i can provide the snapshot.

Got it, use the MyBlog link, there in about me there an option to connectArthur My Blog

Sorry Arthur couldnt find a link in your blog, can you please tell me what information you are looking for from the OLEDB Destination advance editor and i can provide you with the same? Thanks

I will need at least a screenshot as shown here: http://www.google.ca/imgres?q=oledb+destination+ssis&hl=en&safe=off&sa=X&biw=1360&bih=583&tbm=isch&prmd=imvnsfd&tbnid=Hqk2rI_uGGy-hM:&imgrefurl=http://www.resultdata.com/ssis-table-locks-and-a-datasource-procedure/&docid=GD4odb_QEC-etM&imgurl=http://www.resultdata.com/wp-content/uploads/2012/03/sb_ssis_oledb_destination_standard_config.png&w=646&h=632&ei=yBWtT7u6NKmS0QGDtMC3DA&zoom=1&iact=rc&dur=345&sig=117639262121657972501&page=1&tbnh=122&tbnw=125&start=0&ndsp=21&ved=1t:429,r:1,s:0,i:73&tx=72&ty=70 and to contact me use this link: http://geekswithblogs.net/Compudicted/contact.aspxArthur My Blog