Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: SSIS bulk insert issue

    Hello,
    I am trying to run a SSIS package (SQL Server 2005) that will bulk insert about 200 million rows from one database to another database on the same server.
    Our tempdb is swelling up to over 60gb when the package is running. I have added another file to tmpdb but I am running out of space.
    Any thoughts on tweaking a parameter. The insert is a select statement on the dataflow source with the data going to an ole destination.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am no SSIS expert but there is a way to control batch sizes.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If the data is on the same server, WHY would you consider moving it?

    In any case, I'm guessing it's from 1 table right?

    What's the DDL of the table?

    What's the primary Key (IDENTITY no doubt)....

    why not break it up into partitions

    SELECT * INTO partition1 FROM table WHERE id > 1 and id < 25000000
    SELECT * INTO partition2 FROM table WHERE id > 25000000and id < 50000000
    etc
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Brett Kaiser
    If the data is on the same server, WHY would you consider moving it?

    In any case, I'm guessing it's from 1 table right?

    What's the DDL of the table?

    What's the primary Key (IDENTITY no doubt)....

    why not break it up into partitions

    SELECT * INTO partition1 FROM table WHERE id > 1 and id < 25000000
    SELECT * INTO partition2 FROM table WHERE id > 25000000and id < 50000000
    etc
    Brett, what about 25000000? >=

    I assume you are using simple recovery model, and if not, you are dumping the log. Also, perhaps transactions are too large, break them down to smaller chunks.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's covered in etc
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •