change the file name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

change the file name

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-25 : 04:58:43

hi

how i can change the name of the file, like "file200822512 115208"
every day in the dts(tomorrow it'll look "file200822612 115208"
?

i think that i need to write a script for this,no?

sorry of my english

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-25 : 04:59:25

i need to do it in DTS-sql 2000 (not in ssis)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-25 : 07:28:45

DECLARE @file VARCHAR(100)
SET @file='file'+CONVERT(CHAR(9), GETDATE(), 112) +REPLACE(CONVERT(CHAR(12), GETDATE(), 108),':','')
PRINT @file

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 07:36:50

DECLARE @file VARCHAR(100)
SET @file='file'+replace(replace(CONVERT(CHAR(19), GETDATE(), 120),':', ''), '-', '')
PRINT @file

E 12°55'05.63"
N 56°04'39.26"

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-25 : 07:56:13

thank very much,

but my problem is :
* by browse in "Text File(destination)" in DTS , it is all the time point to the old file.
how can i point it to the new one?

for example:
today i export data from DB(Table1) To Text File and his file name is "file200822512 115208."

tomorrow it will point to file20082511 115208,but it need to poin to file20082611 115208

how can i force him to point to the new one?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 08:15:25

Dynamic properties!

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-25 : 09:25:05

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

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-25 : 11:23:28

thanks,it's good.

if my files in the folder
d:\DATA\file1200822511.txt
d:\DATA\file1200822511.trc

where i need to change it in the code?
(every day the date need to change)

thanks?

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-25 : 11:34:06

some question,check it please.
instead GetDate what should i write for get the current date.
where i learen vb script?wich editor i run it to ckeck if it work?

Function Main()
Dim oConn, sFilename

' Filename format - file120081225.txt

sFilename_1 = "file1" & Convert(Varchar(10),GETDATE(),112) &" _152430"
sFilename_2 = "file2" & Convert(Varchar(10),GETDATE(),112) &" _152430"

sFilename_1 = DTSGlobalVariables("D:\DATA\").Value & _
sFilename_1 & ".txt"

sFilename_2 = DTSGlobalVariables("D:\DATA\").Value & _
sFilename_2 & ".trc"

Set oConn_1 = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename_1

Set oConn_2 = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename_2

Set oConn_1 = Nothing
Set oConn_2 = Nothing

Main = DTSTaskExecResult_Success
End Function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 12:28:44

quote:
Originally posted by inbs

some question,check it please.
instead GetDate what should i write for get the current date.
where i learen vb script?wich editor i run it to ckeck if it work?

Function Main()
Dim oConn, sFilename

' Filename format - file120081225.txt

sFilename_1 = "file1" & Convert(Varchar(10),GETDATE(),112) &" _152430"
sFilename_2 = "file2" & Convert(Varchar(10),GETDATE(),112) &" _152430"

sFilename_1 = DTSGlobalVariables("D:\DATA\").Value & _
sFilename_1 & ".txt"

sFilename_2 = DTSGlobalVariables("D:\DATA\").Value & _
sFilename_2 & ".trc"

Set oConn_1 = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename_1

Set oConn_2 = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename_2

Set oConn_1 = Nothing
Set oConn_2 = Nothing

Main = DTSTaskExecResult_Success
End Function

you can use Now()

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-25 : 15:53:59

to visakh16,

do you mean instead
Convert(Varchar(10),GETDATE(),112),i will write
Convert(Varchar(10),now(),112)?

is my program correct?

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-11-26 : 04:06:33

thanks anyway

arunanand
Starting Member

2 Posts

Posted - 2009-04-06 : 12:48:09

Hello,
I was looking at your solution i need something similiar for my DTS Package.
I gives me an error as Text (Source) not defined - what name should I be giving for the Text (Source) name? Should I leave it like this or need to look for soem technical name.
Please guide.

Thanks in Advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 01:43:22

quote:
Originally posted by arunanand

Hello,
I was looking at your solution i need something similiar for my DTS Package.
I gives me an error as Text (Source) not defined - what name should I be giving for the Text (Source) name? Should I leave it like this or need to look for soem technical name.
Please guide.

Thanks in Advance

Post your question as a new topic

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources