From time to time, you will need to dynamically set a variable in the middle of your DTS packages. The following code shows you how easy it is to build a variable dynamically. The variable will be filename which contains the current timestamp. Here is the code:
'*************************
' Sets the file name to the format: MyFile_YYYYMMDD.csv
'*************************
Function Main()
Dim oPkg, oBulkExport
'Set the Path where the file is located
strPath = "c:\MyPath\"
'Next build the different parts of the file name,
'including the prefix and file extension using
'the Now function, which returns the current date and
'the Month(),Day(), and Year() function which
'return the respective datepart.
strPrefix = "MyFile_"
strMonth = Month(Now)
strDay = Day(Now)
strYear = Year(Now)
strFileExt = ".csv"
'If you only require 2 digit years, trim the first 2 digits
'strYear = Right(strYear, 2)
'If the day or month is a single digit, the
'Now function will only return that single digit
'We will append a leading zero to the Month
'and/or Date string if needed
If Len(strMonth) = 1 Then strMonth = "0" & strMonth
If Len(strDay) = 1 Then strDay = "0" & strDay
'Concatenate all parts of the file name with
'the file path in front.
outString = strPath & strPrefix & strYear & strMonth & strDay & strFileExt
'Once the path and file name are determined,
'set the global variable.
'You will still need to set the file name using a Dynamic Properties Task.
DTSGlobalVariables("gv_FileName").Value = outString
Main = DTSTaskExecResult_Success
End Function
As stated in the code, you will still need to execute a Dynamic Properties Task to actually set the file name, as you are only setting a global variable in the package.
