scripting options

[melbert2002]

Hello all,
I have a quesiton. I have a very simple DTS that querries a couple of tables and spits out the results as an csv. That is running just fine. The only thing that I am having a problem with is that this package has to run 3 times a day. How can I add the TIME to the file? I know of a way to add the date, but that is using the FOR statement in a batch file. Is there anyway I can add the time & date to the end of the file name? Like "output_hhmmddmm.csv"

Thanks

Mel

 

[JayKusch]

You could add a last SQL Task and rename the generic csv file you created into a named csv. Lets keep w/ naming the generated csv file as Output.csv

Once the csv has been created, you can run the following code to rename Output.csv to Output_HourMinuteDayMonth or as you had it Output_hhmmddmm.

Here is the code to do the rename. All you have to change is the location of where output.csv live.

DECLARE	@NewFileName	VarChar(30)
DECLARE	@SQL_Command	VarChar(200)

SET	@NewFileName = 'jjk_' 	+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2) + 
				+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2) + 
				+ SUBSTRING(CONVERT(Char,GetDate(),120),9,2) + 
				+ SUBSTRING(CONVERT(Char,GetDate(),120),6,2) + '.csv'

SET	@SQL_Command = 'EXEC Master..xp_CmdShell ' + '''' + 'REN C:\jjk.csv ' + RTRIM(LTRIM(@NewFileName)) + ''''

EXEC	(@SQL_Command)

print	@SQL_Command

Thanks

J. Kusch

 

[chamilz]

same results...

DECLARE @tline varchar(255),
        @ttime varchar(255)

-- Get date and time stamp 

SELECT  @ttime = 
convert(varchar(4),datepart(year,getdate()))+
right('0'+convert(varchar(3),datepart(month,getdate())),2)+
right('0'+convert(varchar(4),datepart(day,getdate())),2)+
right('0'+convert(varchar(3),datepart(hour,getdate())),2)+
right('0'+convert(varchar(3),datepart(minute,getdate())),2)
    
-- rename file

SELECT	
@tline = 'master.dbo.xp_cmdshell "rename H:\MSSQL\output.csv'+' '+'output_'+ @ttime+'.csv"'
exec (@tline)