Bulk Insert vs Flat File Connection Mng

Question

Post reply

Bulk Insert vs Flat File Connection Mng

Lookup_BI

SSCrazy

Points: 2964
More actions
January 8, 2008 at 7:53 am

#119527

Hi,
This might sound like a simple question to you, but I'm trying to figure it out.Maybe someone has an explanation !:ermm:
Which method would you use, Bulk Insert or Drag flat file connection manager to Ole db destination [i]to copy data from file[.csv/.txt] to SQL database[/i] ?
Why ?
What is the differences of these two?
When do I use "Raw File Source" in SSIS ?
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

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

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

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

Heh... neither... use BULK INSERT from T-SQL 😉

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

Lookup_BI SSCrazy Points: 2964 More actions · Answer 2

Hi Jeff,

Thanks for your answer.Any reason why Bulk Insert ?

A friend of mine was asked a similar question on the choices made between [Bulk Insert & Flat File Connection Mng] and the reason's, during a recent Interview he attended.

[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 3

I use the Bulk Insert command from T-SQL... I don't even go near SSIS for the same reason I don't use DTS in 2k... too slow. I can import 5.1 million 20 column rows into a table in 1 minute using Bulk Insert... haven't seen anyone do that with SSIS or DTS. Depending on the file type, it usually takes me less time to write the code, as well.

Other than that, it's a personal preference... 😉

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

Jan Wagner-460740 SSC Eights! Points: 963 More actions · Answer 4

Hi,

actually using SSIS dataflow with option fast load does a bulk insert.

Regards,

Jan

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 5

Thanks for the tip, Jan...

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