Monday, February 18, 2008

Using ActiveX to Dynamically set a filename

From time to time, you will need to dynamically set a filename in the middle of your DTS packages. The following code shows you how easy it is to build a filename dynamically:

' 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
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 a Dynamic Properties Task to actually set the file name, as you are only setting a global variable in the code.