SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005

Fri May 26, 2006 by Mladen Prajdić in sql-server

According to Donald Farmer who visited Slovenia a few months ago importing flat files
with SQL Server Integration Services is the fastest way today to do that.
SSIS now has a special very fast and very optimised library that transforms flat file text
into appropriate SQL Servers datatypes.
On very large files the speed gain is in the range of 7-20% of other ways.

More MSDN info here.

Now how do you get to that cute little option? here you go:

1. Run Visual Studio 2005
2. Under Business Inteligence Project create new Integration Services Project
3. Go to DataFlow tab
4. From Data flow Sources in the toolbox choose Flat File Source
5. Right mouse click on the component and choose Edit
6. Create new Flat file connection manager based on your flat file configuration.
7. Right mouse click on the component again and choose Show Advanced Editor
8. Go to tab Input and Output Properties tab
9. Expand Flat file source output and go to Output Columns
10. For each column set Fast Parse property to true

And there it is.

Enjoy the speed! :))

Performance testing compared to other bulk import methods is here.

Legacy Comments


Denis 2006-05-26	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 Is this faster than BULK INSERT or BCP or is this just the fastest way with SSIS? Denis

Mladen 2006-05-26	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 it was said that it's faster than BCP and BULK INSERT. however i stress that it's for very large text files. for small files there's no difference because of the package execution overhead. i plan to test this stuff in very near future... :)))) haven't had time yet... :(

rockmoose 2006-05-26	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 I like this step, because by the time SSBIS has finished loading and you clicked through the wizards, it is already done ;-) 1. BULK INSERT xxx FROM 'X:\p\f.e' Thx for the update and sharing though Mladen. Couldn't resist a bit of sarcasm, sorry. rockmoose

Mladen 2006-05-26	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 LOL! yeah but you only create the package in the studio. you run it in management studio :)) so HA! :)))))

Vipul Shah 2006-05-30	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 Does this work on all types of columns? I tried to change the property on String (DT_STR) type column and it gave me an error that I couldn't change this custom property on this column. I had to put it back to false again. Is this true? I was able to set the property on eight-byte signed integer [DT_I8] and date [DT_DATE] type columns. Thanks Vipul Shah vshah@taltrade.com

Mladen 2006-05-30	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 No it works on integeres, floats, datetimes and their offspring. I also thought it worked for strings too. Don't know why it isn't supported.

Patrick PK 2006-06-05	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 Wow!!! This is EXTREMELY fast!!! I just did an ND import of approximately 121,000 rows.............LESS THAN 3 SECONDS.

user11 2006-07-20	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 can this be used in a vb.net 2005 application?

Mladen 2006-07-20	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 in what way? Fast Parse is SSIS option and i don't know how you'd run that in VB. What are you trying to do?

Cristian 2006-07-20	very large flat text need more faster way to load I only use sql server 2000, and I have a very large flat text, this file have about 27 000 000 or records, the problem is tha this file have tow tipes or records mixed in the file, that represent information of clientes and his detaills. All recordes have a code or primary key, buy this to kind of records is diferente in the information. I trayed to load the text file, but is very vey slow. I olny can use sql server 2000 and ms studio 2003. Do you have some ideas for resolv this problems? thanks for the attention. Cristian

Mladen 2006-07-21	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 With what method are you loading your file into the database? In SS2k the most popular was BCP-ing or bulk inserting the data into a staging temporary table that has only one varchar(8000) column. That required no column parsing in the import. then you'd parse that column into the table that holds your data.

Mehul Patel 2008-02-26	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 THanks for the information. I wish you used different datatypes when you ran your test. 1 million rows in 10 secs sounds too good. If you use varchars, you can not use the FASTparse option.

Ravi 2008-05-07	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 Hi all, The Bulk insert concept will certainly work fine for inserting data from a text file to Table. But Bulk insert has its own disadvantages, so is there any other way to load the Data from .txt (or .csv) to a Database Table. Please let me know if anyone can solve this..? Regards Ravi

Mladen 2008-05-07	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 you've provided way to little information for any kind of help. post a question in the sqlteam forums and you'll get a faster answer than here.

Ravi 2008-05-07	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 Hi Thanks for the information but let me know any links for updating the Sufficient data?

Navi 2009-08-26	re: SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005 Hi, I have a problem doing this, i have several textfiles that want to be imported to the server. I dont wan't to create several for each single textfiles. And i also want to make this a little more flexible. Is it possible? the question is how can this be recycled Greetings Navi