CODEWITHSUNDEEP
sqlsql-serverssis
William Adomanis
William Adomanis

Reputation: 23

Importing Massive text file into a sql server database

I am currently trying to import a text file with 180+ million records with about 300+ columns into my sql server database. Needless to say the file is roughly 70 GBs large. I have been at it for days and when i get close something happens and it craps out on me. I need the quickest and most efficient way to do this import. I have tried the wizard which should have been the easiest, then i tried just saving as an ssis package. I havent been able to figure out how to do a bulk import with the settings i think would work great. The error i keep on getting is 'not enough virtual memory'. I changed my virtual memory to 36 gigs . My system has 24 gigs of physical memory. Please help me.

Upvotes: 0

Views: 3099

Answers (3)

Techie Joe
Techie Joe

Reputation: 867

I had a similar problem with SQL 2012 and trying to import (as a test) around 7 million records into a database. I too ran out of memory and had to cut the bulk import into smaller pieces. The one thing to note is that all the memory that the import process uses (no matter what manner you leverage) up a ton of memory and won't release said system memory until the server was rebooted. I'm not sure if this is intended behavior by SQL Server but it's something to note for your project.

Because I was using the SEQUENCE command with this process I had to leverage T-sql code saved as sql scripts and then use SQLCMD in small pieces to lessen the memory overhead.

You'll have to play around with what works for you and highly recommend to not run the script all at once.

It's going to be a pain in the ass to break it down in smaller pieces and import it in but in the long run you'll be happier.

Upvotes: 0

Mike
Mike

Reputation: 3811

If you are using BCP (and you should be for files this large), use a batch size. Otherwise, BCP will attempt to load all records in one transaction.

By command line: bcp -b 1000

By C#: 

using (System.Data.SqlClient.SqlBulkCopy bulkCopy = 
new System.Data.SqlClient.SqlBulkCopy(sqlConnection))
{
    bulkCopy.DestinationTableName = destinationTableName;
    bulkCopy.BatchSize = 1000; // 1000 rows
    bulkCopy.WriteToServer(dataTable); // May also pass in DataRow[]
}

Here are the highlights from this MSDN article:

Importing a large data file as a single batch can be problematic, so bcp and BULK INSERT let you import data in a series of batches, each of which is smaller than the data file. Each batch is imported and logged in a separate transaction...

Upvotes: 1

SinisterPenguin
SinisterPenguin

Reputation: 1618

Try reducing the maximum server memory for SQL Server to as low as you can get away with. (Right click the SQL instance in Mgmt Studio -> properties -> memory).

This may free up enough memory for the OS & SSIS to process such a big text file.

I'm assuming the whole process is happening locally on the server.

Upvotes: 0

Related Questions