DTS Package – Move Imported Data File to an Archive Directory

‘ Rename File from Connection
Option Explicit

Function Main()

Dim oPkg
Dim oConn
Dim oFSO
Dim oFile
Dim sFilename
Dim sFileDestination
Dim sMonth
Dim sDay
Dim sYear
Dim sHour
Dim sMinute
Dim sSecond

‘ Get reference to the current Package object
Set oPkg = DTSGlobalVariables.Parent

‘ Get reference to the named connection
Set oConn = oPkg.Connections(“Text File (Source)”)

‘ Get the filename from the connection
sFilename = oConn.DataSource

sMonth = CSTR( MONTH(Now()))
sDay = CSTR(DAY(Now()))
sYear = CSTR(YEAR(Now()))
sHour = CSTR(HOUR(Now()))
sMinute = CSTR(MINUTE(Now()))
sSecond = CSTR(SECOND(Now()))

sFileDestination = DTSGlobalVariables(“dts_ArchiveLocation”).Value & DTSGlobalVariables(“dts_FileName”)

‘MsgBox sFileDestination & sMonth & sDay & sYear & “.TXT”

‘ Rename the file
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
‘oFSO.MoveFile sFilename, sFilename & “.bak”
oFSO.MoveFile sFilename, sFileDestination & sYear & sMonth & sDay & “_” & sHour & sMinute & sSecond & “.TXT”

‘ Clean Up
Set oConn = Nothing
Set oPkg = Nothing
Set oFSO = Nothing

Main = DTSTaskExecResult_Success
End Function