DTS to export to excel with getdate() as part of the file name?

Question

Post reply

DTS to export to excel with getdate() as part of the file name?

jamie Gerardo

SSC-Addicted

Points: 487
More actions
March 28, 2006 at 2:07 pm

#68782

Hello! What I need to do is export data on a daily basis but not overwrite the file everyday. Ideally, we would like to make the current date as a part of the file name. It doesn't necessarily have to be an .xls or .csv, but a format that could be imported into excel easily. Thanks for the help! Jamie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ghanta Bro Ten Centuries Points: 1079 More actions · Answer 1

Why don't you export the file and then rename the file appending date and archive it to some other folder.

jamie Gerardo SSC-Addicted Points: 487 More actions · Answer 2

That would be because our CFO/CIO wants EVERYTHING automated . I was wondering if I could first setup a task to basically copy and rename the file then clear out the old data? I don't know... I'll be working on it. Thanks! Jamie

philcart SSC-Forever Points: 47795 More actions · Answer 3

You can use an activexscript, or a dynamic properties task to change the source filename.

The following link shows how to do it in script.

http://www.sqldts.com/default.aspx?200

--------------------
Colt 45 - the original point and click interface

JC-255200 SSC Veteran Points: 289 More actions · Answer 4

hey dude try this

Put the ffg in a SQL Task, as the final step of your dts

--- copyfile from after extract to temporary location

EXECUTE master..xp_cmdshell 'Copy \\server1\dts*.xls ,\\server1\temp '

-- set file name

SELECT @vFile = Replace('Prefixgoeshere'+SUBSTRING(CAST(GETDATE() AS CHAR(10)),1,6)+ '.xls',' ','')

SELECT @vRename = 'Ren \\server1\dts*.xls *.* ' + @vFile

-- renames file in dos

EXECUTE master..xp_cmdshell @vRename

-- mail file to yourself

declare @vsubjectm varchar(255),@vattach varchar(255),@dte varchar(255)

set @dte =UPPER( SUBSTRING(CAST(GETDATE() AS CHAR(10)),1,6))

set @vsubjectm = 'Whatever REPORT - ' + @dte

set @vattach ='\\server1\'+ Replace('Prefix'+SUBSTRING(CAST(GETDATE() AS CHAR(10)),1,6)+ '.xls',' ','')

EXECUTE master..xp_sendemail

@recipients =' ,mail@2me.com',

@copy_recipients = ,

@subject = @vsubjectm,

@message = 'Please find attached Daily Report ',

@attachments = @vattach

-- delete file after mail

EXECUTE master..xp_cmdshell 'Del \\server1\temp\*.xls'

works for me!