SQL Server Performance

Dynamic File Name

Discussion in 'SQL Server 2005 Integration Services' started by AstoneJ, Aug 14, 2007.

  1. AstoneJ New Member

    What is the easiest way to pull in a text file to sql server on a daily basis
    we get a new file every day that i want to append to an existing table (needs to be transformed, obviously)
    lets say the file gets placed in C:DataLoadsDataFileYYYYMMDD.txt (its actually an as4, but it looks like i can treat this like a text file.)
    i want to make a job that runs overnight that loads whatever data is in today's file, what is the easiest way to tell sql server to get only today's file?
  2. ndinakar Member

    You can use BCP utility to load a text file into a table. Get the command working first, then you can schedule a job to run every day and pick up the file from the directory. You can set the directory path as a variable.Declare
    @Filename varchar(50)Set
    @filename = 'C:DataLoadsDataFile' + REPLACE(convert(Varchar(10), Getdate(), 126), '-', '') + '.txt'
  3. AstoneJ New Member

    good idea, might you know where i can find an article telling BCP that the file is a fixed width text file, this many goes to col1, all the formatting issues, etc
  4. satya Moderator

    You could take help of FORMATFILE in BCP, refer to the updated books online in this regard.

Share This Page