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

<< Do you know when your data was modified? Or do you? | Home | Column values Concat the SQL Server 2005 way >>

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

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.

Feedback

# 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

5/26/2006 3:23 PM | Denis

# 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... :(

5/26/2006 3:34 PM | Mladen

# 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

5/26/2006 10:40 PM | rockmoose

# 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! :)))))

5/26/2006 11:20 PM | Mladen

# 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

5/30/2006 5:29 PM | Vipul Shah

# 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.

5/30/2006 5:33 PM | Mladen

# SSIS: Fast Parse

I recently spotted a blog post from Mladen talking about the Fast Parse facility within SSIS....

5/30/2006 1:50 PM | Jamie Thomson - SSIS Jibber Jabb

# 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.

6/5/2006 10:29 PM | Patrick PK

# 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?

7/20/2006 9:52 AM | user11

# 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?

7/20/2006 11:18 AM | Mladen

# 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

7/20/2006 9:53 PM | Cristian

# 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.

7/21/2006 11:01 AM | Mladen

# Bulk Insert時の性能比較(オプションの有効活用がポイント)

Bulk Insert??????(???????????????)

7/23/2006 9:40 AM | 米田 Blog ( SQL Server MEMO )

# SSIS: Fast Parse

I recently spotted a blog post from Mladen talking about the Fast Parse facility within SSIS. it highlighted

1/16/2007 1:07 PM | SSIS Junkie

# 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.

2/26/2008 4:28 AM | Mehul Patel

# 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

5/7/2008 12:26 PM | Ravi

# 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.

5/7/2008 12:29 PM | Mladen

# 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?

5/7/2008 1:40 PM | Ravi

# 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

8/26/2009 12:34 AM | Navi

Comments have been closed on this topic.

I want some Moore

My Links

Advertisement

News

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

Related Links

Feedback

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

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

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

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

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

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

# SSIS: Fast Parse

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

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

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

# very large flat text need more faster way to load

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

# Bulk Insert時の性能比較(オプションの有効活用がポイント)

# SSIS: Fast Parse

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

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

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

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

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