Post reply

SSIS Vs sql import export performance

  • September 22, 2011 at 1:59 pm

    #142929

    Hi All,

    I am using sql 2008. I have to import 130 million record into sql server. source and destination both are both sql server 2008. I make a job to import data and in that job I call ssis pkg. SSIS pkg don't have any transformation. it only have simple two transformation i.e sql source and sql destination(using OLEDB connection). I'm getting very poor performance with this job

    while, when I'm using sql import export wizard is very fast. can some one help me out what happening here.

    Thanks

    Sneh

  • September 22, 2011 at 2:28 pm

    #1385446

    The wizard is using SSIS, but probably with different destination options.

    Go to the destination and make sure data access mode is Table or View - fast load. Set maximum insert commit size to a million or so (1000000). Make sure it grabs a table lock, and you'll probably want to keep nulls to avoid forced defaulting.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 22, 2011 at 3:03 pm

    #1385466

    Thanks for looking into this.

    Fastloadmaxinsertcommitsize is 2147483647

    Accessmode is openrowset using fastload

    fastloadoption is TABLOCK,CHECK_CONSTRAINTS

    Thanks

    Sneh

  • September 23, 2011 at 3:01 pm

    #1386035

    snsingh (9/22/2011)

    Thanks for looking into this.

    Fastloadmaxinsertcommitsize is 2147483647

    Accessmode is openrowset using fastload

    fastloadoption is TABLOCK,CHECK_CONSTRAINTS

    Thanks

    Sneh

    Open Rowset? What are you loading this from?

    What are your options in this drop down?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

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

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