Variable name for my output file

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)

Variable name for my output file

Author

Topic

Antonio
Posting Yak Master

168 Posts

Posted - 2006-07-06 : 12:58:57

Hi there,

I have a simple DTS package (SQL Server 2000) which outputs some data to a CSV file. What I want to do is save the CSV file with today's date as part of the name. How to do this?!

Do I have to use ActiveX script to accomplish this?

Thanks in advance.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-07-06 : 14:09:32

Yeah, create an activex task containing a sub to do something along these lines:


Dim objPackage
Dim objTextCon
Dim datDate
Dim strDate
Dim strYear
Dim strMonth
Dim strDay
Dim strFilePath
Dim strFileName
Dim strFileSuffix

strPath = DTSGlobalVariables("gv_FilePath").Value
strFilename = DTSGlobalVariables("gv_FileName").Value

datDate = Date()
strYear = CStr(Year(datDate))
strMonth = CStr(Month(datDate))
strDay = CStr(Day(datDate))
strDate = strYear & strMonth & strDay

strFileName = strFileName & "_" & strDate & ".csv"
strFileName = strFilePath & strFileName & strFileSuffix

Set objPackage = DTSGlobalVariables.Parent
Set objTextCon = objPackage.Connections("MyTextConnection") 'Substituting the name of your connection!!
objTextCon.DataSource = strFileName

Set objTextCon = Nothing
Set objPackage = Nothing

Then create a precedence constraint to ensure it runs before your (I assume) transform task.

Mark

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-07-06 : 15:35:54

I do a little differently, I setup the dates on the DTS Global Variable and use that on each step of the package for other reasons.
If you take this route, use the first script as the first step of the dts and the second one after you have exported the data to the file.

See scripts below:

This code will set your DTS Global Variables (date variables):
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
if not (isdate(dtsglobalvariables("YesterdaysDate"))) then
dtsglobalvariables("YesterdaysDate")=formatdatetime(date()-1,2)
dtsglobalvariables("DateInPreviousMonth") = DATEADD ("m", -1, date ())
end if
dtsglobalvariables("TodaysDate") =formatdatetime(cdate(dtsglobalvariables("YesterdaysDate"))+1,2)
Main = DTSTaskExecResult_Success
End Function

This code will rename your file:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

theday = Day(dtsglobalvariables("TodaysDate"))
themonth = Month(dtsglobalvariables("TodaysDate"))
theyear = Year(dtsglobalvariables("TodaysDate"))
If Len(theday) = 1 Then theday = "0" & theday
If Len(theprevday) = 1 Then theprevday = "0" & theprevday
If Len(themonth) = 1 Then themonth = "0" & themonth
filetoget1 = "Report.XLS"
filetoget2 = "Report_" & themonth & theday & theyear & ".XLS"

Dim fs

Set fs = CreateObject("Scripting.FileSystemObject")

destinationfilename = "C:\dir1\subdir1\" & filetoget2
if fs.fileexists(destinationfilename) then fs.deletefile destinationfilename

templatefilename = "C:\dir1\subdir1\" & filetoget1
if fs.fileexists(templatefilename) then fs.copyfile templatefilename, destinationfilename

Main = DTSTaskExecResult_Success
Main = DTSTaskExecResult_Success
Main = DTSTaskExecResult_Success

End Function

---

Thanks!
Igor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources