0

I have a tpcds dsdgen generated data about 100GB, and each dat file is around 20GB+, I have exhaustively searched how to load flat file into sql-server 2016, and i tried importing it using SSIS package and enabled fast parse on all the decimal fields. Still it takes lot of time to load into a table. Its taking 24hrs and continuing and just loaded 9,700,000 records of a table only,

is the above the ideal way, or what could be the problem.

please help me, Iam stuck with this problem and also new to MSSQL world.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Nawap
  • 43
  • 5
  • 2
    SSIS can load millions of rows per minute. Make sure you are using an OLE DB destination with the fast load option with a non-zero maximum insert commit size and table lock options. Ideally, minimal indexes on the target table too. – Dan Guzman Jun 23 '18 at 12:38
  • i have an OLE DB destination , i have enaled fast-load(the second option in the dropdown), i removed constraint check, i kept table lock, and increased batch size and max insert commit size, still, this issue is prevaling – Nawap Jun 23 '18 at 12:45
  • 2
    How many indexes on the table? What is the commit size? – Dan Guzman Jun 23 '18 at 13:12
  • 1
    Are the files, the SQL Server and the SSIS runtime all on the same host? Are the data in the files in a known order? – Nick.Mc Jun 23 '18 at 14:09
  • There is no indexes, and commit size is 2000000000, batch size is 100000, Thanks for the help, i cleared cache, and buffer for every 5min. Things was sorted, but i still dont know wether that solved the problem. Could anyone explain? – Nawap Jun 25 '18 at 04:22

0 Answers0