DTS job - loading data from CSV file

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  November 28, 2006 at 7:08 am

  #121679

  Guys,

  I relatively new to sql server 2005, I am trying to create a job which grabs csv file from a location c:\data and loads it into a table. This job should run every night. How can I set it up so that field is mapped to the value in the csv file.

  Any input/suggestions would help

  Thanks

• Usha-375170

  SSC-Addicted

  Points: 413

  More actions

  November 28, 2006 at 3:30 pm

  #674549

  Hi,

   

  1.Add a data flow task.

  2.In data flow,select flat file source

  3.Give a name for connection manager.

  4.Click on browse,select the file file c:\data,

  5.Select the checkbox if the csv has column names as headers

  6.Click on preview check if column have been mapped.

  7.Select OLEDB destination.select the table in which you have to load.

  8.Execute the job

  Regards

  Usha

• RML51

  SSCarpal Tunnel

  Points: 4410

  More actions

  November 29, 2006 at 12:03 pm

  #674766

  You may be able to use something like the the following, too.

   

  BULK INSERT  NorthWind.dbo.[table_name]

     FROM 'C:\InputFile.csv'

      WITH (FieldTerminator = ',',

     FirstRow = 2)  -- Use if csv file has column names