importing large flat files

Question

importing large flat files

coenie-353912

SSChasing Mays

Points: 615
More actions
June 1, 2011 at 5:33 am

#145611

I am importing a large flat file with over 80 millions records using SSIS into a SQL table, it takes a very long time to import. I have tried to get the job to run faster, I have tried the FastParse option as well but that does not work for varchar data types.
Any suggestions

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

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

coenie-353912 (6/1/2011)
I am importing a large flat file with over 80 millions records using SSIS into a SQL table, it takes a very long time to import. I have tried to get the job to run faster, I have tried the FastParse option as well but that does not work for varchar data types.
Any suggestions

First, please define what you mean by a "long time".

Next, how many indexes do you have and how close is the order of the clustered index to the order of the data being imported?

--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)

coenie-353912 SSChasing Mays Points: 615 More actions · Answer 2

long time = 120 minutes +

I do not have any indexes on the destination table, this is a straight import from a flat file with a specified column delimeter.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

there are a number of things that you can do to speed up an SSIS package.

there is a great document here that outlines some of the main ones;

http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx%5B/url%5D

basically it is about optimising the source and destination and moving the least amount of data as possible.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Based on item #7 of the article that SteveB posted, I'd like to make a couple of recommendations.

1. Set the database you're loading to to "Bulk-Logged" recovery.

2. Lookup "minimal logged bulk copy [SQL Server]" and study the requirements for minimally logged bulk copies.

3. Use a BULK INSERT in a stored proc to load the data according to the requirements in Item #2 above.

--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)