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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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