Post reply

ssis data flow task taking an extremely long time .

  • April 22, 2013 at 2:03 pm

    #276804

    Hello everyone .

    I have a log table that I am planning to import from my Transaction Database to my reporting Database.

    The columns of the table are as follows:

    Id,Varchar1 , Varchar2, Time1, Time2

    XML structures are stored in Varchar1 & varchar2

    I am using a SSIS package and a data flow task with an OLEDB source and destination.

    There are close to 600000 records in the table and its taking an extremely long time to transfer.

    The queries that I tried so far in my OLEDB source are :

    1)select Id, XML1, XML2, Time1, Time2 where Time > 'XXXX' and Time2 < 'XXXX'

    Converting Varchar to xml

    2) select Id, CONVERT(XML,CONVERT(NVARCHAR(max),Varchar1, 0)), CONVERT(XML,CONVERT(NVARCHAR(max),Varchar2, 0))

    , Time1, Time2 where Time > 'XXXX' and Time2 < 'XXXX'

    Both the queries take me an extremely long time.

    Does anyone have a better solution/approach to achieve my task ?

  • April 22, 2013 at 8:03 pm

    #1608910

    Are both databases on the same server?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 23, 2013 at 1:08 am

    #1608954

    Taking SSIS out of the equation for a moment, do the queries run quickly in SSMS?

    You need to do some detective work to understand exactly where the bottleneck is.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • April 23, 2013 at 7:22 am

    #1609040

    @jeff : The databases are on two different servers.

    @Phil : Thank you ! . I will check the queries.

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

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