ssis package so slow-coping table from sql 2 oracl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

ssis package so slow-coping table from sql 2 oracl

Author

Topic

AustraliaDBA
Starting Member

38 Posts

Posted - 2012-06-20 : 02:00:15

Hi All,

all i am trying to do is copy data from a table from SQL Server to Oracle. table has 5000 rows only but ssis package takes ages to complete the task. any idea why is it so. if i do the same from sql to sql it takes less than couple of mins. please help

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-20 : 10:31:32

1. where is the oracle table located: remote or local
2. does the oracle table have embedded logic such as triggers etc

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 22:46:57

whats the task you're using? are you doing it in control or data flow? is there some transformations involved in between or is it a straight move?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AustraliaDBA
Starting Member

38 Posts

Posted - 2012-06-21 : 18:55:58

oracle database is on seperate server but both sql and oracle db servers are on same domain. i am only changing datatype in data conversion nothing else. i am doing everything in data flow task

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 20:00:18

are you using fast load option?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AustraliaDBA
Starting Member

38 Posts

Posted - 2012-06-22 : 00:31:34

my source is sql and destination is oracle. at destination i can't see fast load option

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 23:04:46

oh ok destination is oracle. I thought its other way around.

if its just a direct pull with only data conversions in between why not do it in T-sql scripts by adding Oracle as a linked server and using

INSERT ..SELECT.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources