I will second Pavel in saying that SSIS is probably the best way to go in general.
With that said, I want to point out that there are many, many ways to import CSV files into SQL Server. You can use bulk insert, BCP, DTS, or opendata source (I wrote an article about that here: http://www.sqlservercentral.com/articles/61552/).
You could also write a custom import routine in another language like C# or Python (http://www.simple-talk.com/sql/database-administration/python-for-the-sql-server-dba/). I have found writing custom import scripts in python handy on a couple of occassions, but those involved cases where I needed to do significant preprocessing of the data before it came into SQL Server.
There is another very similar question on this site at http://ask.sqlservercentral.com/questions/3519/import-data-from-a-csv-file
answered Nov 15, 2010 at 10:00 AM
If you are dealing with huge data, its better to switch on to SIMPLE RECOVERY MODE to restrict the log growth.
Remember you cannot do more complex operation with any of the methods above, Prefer SSIS when your do complicated data transforms or operations.
answered Nov 15, 2010 at 12:58 AM