|
If you are receiving
a feed file with the same name every day with ftp transmission, it
is necessary either to delete or archive the file after processing
it. The following is a T-SQL sample script to datestamp a filename and archive it:
-- xp_cmdshell with dynamic command string
DECLARE @SQLCommand varchar(256), @Today varchar(23)
SET @Today =convert(varchar(23), getdate(), 121)
SELECT @Today=REPLACE(@Today,'-', '_')
SELECT @Today=REPLACE(@Today,':', '')
SELECT @Today=REPLACE(@Today,'.', '')
SELECT @Today=RTRIM(REPLACE(@Today,' ', '_'))
SET @SQLCommand = 'ren D:\Data\Import\Accounting\Finance.csv ' +
'Finance'+@Today + '.csv'
PRINT @SQLCommand
-- ren D:\Data\Import\Accounting\Finance.csv Finance2010_01_08_141903277.csv
EXEC master.dbo.xp_cmdshell @SQLCommand
-- For a different server include UNC prefix: \\ServerName\D$ to replace D: .
-- You can also move the renamed file into an Archive folder
SET @SQLCommand = 'move D:\Data\Import\Accounting\'+ 'Finance'+
@Today + '.csv D:\Data\Import\Accounting\Archive\'
PRINT @SQLCommand
-- move D:\Data\Import\Accounting\Finance2010_01_08_141903277.csv
-- D:\Data\Import\Accounting\Archive\
EXEC master.dbo.xp_cmdshell @SQLCommand
|