DTS Scripting - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Scripting

Author

Topic

jemacc
Starting Member

42 Posts

Posted - 2004-05-03 : 19:32:29

I have a DTS job that exports the results based on a store procedure to a text file destination

Here is the problem.
I use the following activex script to name the file, for example credits_20050502.txt for each day the job is run.

' Pkg 200
Option Explicit

Function Main()
Dim oConn, sFilename

' Filename format -Credits_yyyymmdd.txt
sFilename = Right(Year(Now()),4)&"Credits"&"_"
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename & ".txt"

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

Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function

What happens is the sometimes the file does not change name to current date, but keeps the date from previous run. Can some one help?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-05-03 : 21:58:58

What time of day is it run ? What triggers the job ? SQL Server's scheduler, or something external ?

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-03 : 22:09:22

You can simplify this to
sFilename = Right(Year(Now()),4)&"Credits"&"_" &_
right("0" & Month(Now()),2) &_
right("0" & Day(Now()),2) &_

Maybe it's running twice on the same day?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-05-03 : 22:12:33

quote:
Originally posted by nr

Maybe it's running twice on the same day?

That's my theory. It's running at midnight, but midnight is determined by another machine with a clock going out of sync. Or it's run just before midnight but the job sometimes takes a while so it crosses the day.

Long shots... but worth an ask

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources