SSIS Package Optimization

  • 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?

  • 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

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

    ---

  • 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thanks Koen for quick info. Let us try to upgrade SSIS and check.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply