convert flat file (.cvs) to SQL Server 2005 using SSIS

Question

convert flat file (.cvs) to SQL Server 2005 using SSIS

Dee Dee-422077

Ten Centuries

Points: 1082
More actions
February 1, 2010 at 6:13 pm

#138565

Hi,
I have a cvs flat file and I would like to bring it to SQL Server using SSIS. Can anyone please direct me to some example that I can follow throught.
Can I use import option to perform the same task? Any link to example would be greatly appreciated.
Which option is a better way to perform this task?
Thanks so much for any help.
Thanks,
Dee

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

SSIS is a a good tool to do that. IF you aren't doing any other transforms to the data then the bcp utility or bulk insert might be a better option. See Books on Line (BOL) for examples.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

If you decide not to use the BCP Utility which has it's advantages you may want to consider using the SSIS Import and Export Wizard.

One way to access this Utility is:

Start|Run

In the dialog box specify DTSWizard and specify Microsoft Excel as the Data Source.

You can specify an existing table or create a new table as the desination.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dee Dee-422077 Ten Centuries Points: 1082 More actions · Answer 3

Thank you all.

I tried to use SSIS Import and Export Wizard and data import it great except that it bring all the Quotation mark in. How do I get rid of it?

Ex: flat file:

"data1","data2","data3","",""

after import to SQL database

Field1 Field2 Field3 Field4 Field5

"data1" "data2" "data3" "" ""

Thanks,

Dee

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

You need to change the delimeter so that it does not import the "".

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Ummmm.... isn't there a text qualifier setting that can be enabled?

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