Rudy Rodarte . com


This article was written on 22 Jan 2010, and is filled under SQL Blogs.

Current post is tagged

, , , ,

Using ActiveX to Dynamically set a Variable in an SQL Server DTS Package

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.


Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter

Leave a Reply