How do I read a CSv file into SQL Server 2005

use the import export manager and then save the ssis.

it can then be scheduled as needed.

right click the db you want to import into, select import, and follow the wizard to import the csv file.

i think the best solution will be to create a SQL job for doing the import........ for that purpose you will have to place the CSV file on the server and create a stored procedure which will import/process the data in the CSV file and take action on the data
the following link provides information how you can process your csv file
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

I can't imagine you'd need a job to do this if it was a one-time deal...

If you would like to use C# to process the file the story would be slightly different. Please advise us if so...

In short, it would include reading a textfile line by line and splitting each column, e.g.
using (StreamReader sr = new StreamReader("TestFile.txt")) {
                string line;
                string [] split;
                while ((line = sr.ReadLine()) != null) {
                    split = line.Split(new Char [] {'|'});
                     // do your own logic, e.g. calling another method that executes the SQL insert
                    }
                }
            }

Do the inserts with the SQLClient and move the file afterwards...

its not a one time thing ......... the author clearly states that the information needs to be processed every hour... so the sql agent is the best approach

I know, that is why a proposed the SQL Agent job first. But the C# code could also be implemented in such a way that it could be fired every hour, e.g. by implementing it as a windows service. But from what we know about the case, I would clearly believe that the implementation through SQL Server would be the most suitable one.

My requirement have changes. Now the user wnats to use excel instead of CSV. I was able to create the SSIS and schedule it to run, but when I try to run it it craps out on me. I used the solution below. Also, I need to move the files to an archive folder after processing.

If you would like to use SQL Server functionality you could (as mentioned by several experts over):
- From SQL Management Management Studio right click the database
- Choose 'Tasks'
- Choose 'Import Data'
- Follow the instructions from the 'SQL Server Import and Export Wizard'
  - From 'opening' page just press 'Next'
  - In 'datasource' page choose 'Flat file source', point to a sample file and vary the miscellaneous settings until you are satisfied. Use the preview ability extensively :) Be sure to set the column delimiter to '|' if it is not automatically interpreted
  - In 'destination' page specify the database where the data should be imported to
  - In the 'source table' page be sure to check mappings from file to table
  - In the 'save and run' page be sure to check the option 'Save SSIS Package'
  - In the 'save ssis package' page give the import job a sensible name
  - Press finish
- Check the status of the initial run, and continue if its ok
- Again from SQL Management Management Studio expand the 'SQL Server Agent'
  - Right click 'Jobs' and choose 'New Job'
  - Give the job a sensible name
  - Go to 'Step' and press 'New'
     - Give the step a sensible name
     - Choose 'SQL Server Integration Services Package' as type
     - Specify the database from the previous step and choose the SSIS package you made earlier
     - In 'Datasources' page check the values for source & destination
     - Press finish
  - Go to 'Schedules' and press 'New'
     - Give a name
     - Set the frequency
     - Press Ok
- Press OK again and you are done :)

Hi blujitters

Use the the same wizard as before to create the job, but choose Excel file as source in stead of Flat file.

Please explain in a bit more detail, if this does not work as intended (sample file, error message, etc).

The movement of the file to a archive folder could be added as a second task in the same job.

I want to copy data from files in a directory into sql server 2005. After the data has been copied, I want to move the file to an archive folder. I need this process to run every hour.
I am trying to acheive this with SQL Server Business Intelligence. But I can't seem to get it to work.  
Here is the link to the tutorial that I based used http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html.
I have attached a file which shows the error, Excel connection manager properties and my connection string
SSISError.doc

I changed my connection string to Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="EXCEL 8.0;HDR=YES"; and now I get the error in the last image on the attached file.
SSISError.doc

Okay I got the import working. The http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html. tutoriial actually works. I was missis an step. Now I need to incorporate moving the files to another folder after it has preocess.

If you have any problem with moving the file, a small scriptsample is given here:
https://www.experts-exchange.com/questions/22867806/Job-schedules-move-files.html

I have evrything working in Business Intelligence Development Studio but it does not work when i create the job in SQL Server 2005. redpipe, i'll take a look at the link you sent to me.

I am not able to see it from your attachment, but compared to the sample at the bi-polar... site you sent, you are missing something in your connectionstring.
- Have you created a package variable with the name FileName as stated in step 13 and 14
- Have a look at http://www.connectionstrings.com/excel for valid connectionstrings
- In your attachment there is no escapement of the double quotes ( \" ), e.g.
  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="EXCEL 8.0;HDR=YES"  as given in you word file should be
  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties=\"EXCEL 8.0;HDR=YES\"  or in your sample
  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";

If this doesn't help, please give me a description of the errors that occur when you run the job in SQL Server, but not in SSIS.

The package is created as stated earlier.My import works and all files are moved to the archive folder in Business Intelligence Development Studio. The problerm now is that when I create and try to run the job in SQL Server 2005 Studio I get the error "Execution of the job 'ImportData' failed. See history."  

I have attached the error log.
errorLog.log

my suggestion will be a little bit different
i will be using a different approach - using the openrowset to read the data from the csv file to a datatable
i will create an sp, create a dynamic query and pass the cvs file path to it to read from it

have a look at the following link it will help you in reading the data from the csv
http://www.databasejournal.com/features/mssql/article.php/10894_3331881_2/OpenRowSource-and-OpenRowSet-in-SQL-Server-2000.htm

in case there are multipe files to be read then create a logic to pass the list of csv files and call the sp multiple times
you can automate the process by using a job in sql server

I am getting an error when the automated task tries to run. See attachment in my previous comment.