Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package Optimization Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 11:59 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
Folks,

We are taking Oracle Source data using Data Flow Task in which Data Reader as Source [We have Separate DSN for this] and OLE DB as destination to store the same data into SQL Server table. There are some date condition from which we are extracting data.

EX: SELECT <COLUMN NAMES> FROM <TABLE> WHERE MODIFIED_DATE BETWEEN '2013-05-01 00:00:00.000' AND '2013-05-05 00:00:00.000' -- Actually it should returns 250 rows of record and we get so.

If we using package level its taking nearly 30 to 35 minutes for extracting the data. But if i use Microsoft Query based on the same DSN [Using MS-Office Excel 2007 ODBC] it's taken only 05 minutes to get the data.

Can i know are there any possible way how to optimize my SSIS package by using Data Reader/package level?
Post #1450894
Posted Tuesday, May 14, 2013 3:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 12,966, Visits: 10,742
Why don't you use the Oracle OLE DB provider to read the data?
Another option is to use the specialized (but free) Oracle adaptors from Attunity: http://www.microsoft.com/en-us/download/details.aspx?id=29283




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1452460
Posted Wednesday, May 22, 2013 3:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
Hi Koen,

Thanks for your reply. Currently, i am using 2005 Version. But it seems the link is for 2012 i guess.

Can you suggest any option available on SSIS 2005.

---
Post #1455369
Posted Thursday, May 23, 2013 1:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:29 PM
Points: 12,966, Visits: 10,742
sqlusers (5/22/2013)
Hi Koen,

Thanks for your reply. Currently, i am using 2005 Version. But it seems the link is for 2012 i guess.

Can you suggest any option available on SSIS 2005.

---


The 1.0 release seems to be unavailable. So you should try the Oracle OLE DB provider.
Or upgrade SSIS to a recent edition.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1455852
Posted Thursday, May 23, 2013 1:36 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
Thanks Koen for quick info. Let us try to upgrade SSIS and check.
Post #1455855
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse