Thank you Alex. I am new in VB and I did not write that ActivexScript(VB) in the DTS package and I do not know how to write that script in VB.Net. I am attaching one script here so that you can guide me. I really appriciate your time and effort. Thank you.
*******
'*******************************************************************************
' Visual Basic ActiveX Script
'
' This script determines is a previous archive of the Servers Alive log file
' was previously completed for the requested month. If the requested month
' was not previous archived, the processing continues to archive the data.
'*******************************************************************************
'*******************************************************************************
'
'*******************************************************************************
Function Main()
dim objFSO 'file system object to verify archive file does not exist
dim objEmail 'email object for error messages
dim strArchiveMonth 'requested month to archive
dim strArchiveBegin 'date to begin archiving data
dim strArchiveEnd 'date to end archiving data
dim strArchiveLog 'date to instert in log file name
dim strArchiveFile 'archive file name
dim strArchiveDir 'archive directory name
dim dtCurrDate 'current date
dim dtWorkDate 'work field for calculating archive date
dim strWorkYYYY 'work field for archive year
dim strWorkMM 'work field for archive month
dim strArchiveDay 'day for archiving
dim strErrorSubject 'error email subject
dim strErrorbody 'error email body
dim strWorkArchiveName 'full name of archive file
gv_ArchiveMonth = DTSGlobalVariables("gv_ArchiveMonth").Value
dtCurrDate = date()
strArchiveDay = "/01/"
strErrorSubject = ""
strErrorbody = ""
If gv_ArchiveMonth = "mm/yyyy" Then
'Default archive date (mm/yyyy) supplied, archive log data from month three months past
dtWorkDate = dateadd("m", -4, dtCurrDate)
strWorkYYYY = CStr(year(dtWorkDate))
strWorkMM = CStr(month(dtWorkDate))
'Make sure month is two digits to ensure consistent archive file name
If len(strWorkMM) = 1 Then
strWorkMM = "0" & strWorkMM
End If
strArchiveBegin = strWorkMM & strArchiveDay & strWorkYYYY
strArchiveLog = strWorkYYYY &strWorkMM
dtWorkDate = dateadd("m", -3, dtCurrDate)
strWorkYYYY = CStr(year(dtWorkDate))
strWorkMM = CStr(month(dtWorkDate))
strArchiveEnd = strWorkMM & strArchiveDay & strWorkYYYY
Else
'Specific month requested to be archived
strWorkMM = left(gv_ArchiveMonth, 2)
strWorkYYYY = right(gv_ArchiveMonth, 4)
dtWorkDate = strWorkMM & strArchiveDay & strWorkYYYY
If IsDate(dtWorkDate) and strWorkMM < CStr(Month(dtCurrDate)) and strWorkYYYY <= CStr(Year(dtCurrDate)) Then
'Valid date format supplied
strArchiveBegin = dtWorkDate
strArchiveLog = strWorkYYYY & strWorkMM
dtWorkDate = DateAdd("m", 1, strArchiveBegin)
strArchiveEnd = CStr(Month(dtWorkDate)) & strArchiveDay & CStr(Year(dtWorkDate))
Else
'Invalid date format must be mm/yyyy and less than current month
strErrorSubject = "SA Archive Error - Invalid Date Parameter"
strErrorbody = "Servers Alive log table archive request failed due to invalid date parameter of " & gv_ArchiveMonth & " supplied. Correct parameter input for the gv_ArchiveMonth global variable of DTS package, Servers Alive - Archive, then re-execute package."
End If
End If
If strErrorSubject = "" Then
'Determine if archive file already exists
Set objFSO = CreateObject("Scripting.FileSystemObject")
strArchiveFile = DTSGlobalVariables("gv_ArchiveFile").Value
strArchiveDir = DTSGlobalVariables("gv_ArchiveDir").Value
strWorkArchiveName = strArchiveDir & strArchiveFile & strArchiveLog & ".csv"
If objFSO.FileExists(strWorkArchiveName) Then
strErrorSubject = "SA Archive Error - Archive File Exists"
strErrorbody = "Servers Alive log table archive request failed because " & strWorkArchiveName & " exists."
End If
End If
'Process error
If strErrorSubject = "" Then
'Set global variables to use later to archive SA log file
DTSGlobalVariables("gv_StartDate").Value = strArchiveBegin
DTSGlobalVariables("gv_EndDate").Value = strArchiveEnd
DTSGlobalVariables("gv_LogFile").Value = strArchiveFile & strArchiveLog & ".csv"
DTSGlobalVariables("gv_LogDate").Value = strArchiveLog
Main = DTSTaskExecResult_Success
Else
'Set archive request month/year back to default
DTSGlobalVariables("gv_ArchiveMonth").Value = "mm/yyyy"
'Send error email
Set objEmail = CreateObject("CDO.Message")
objEmail.From = DTSGlobalVariables("gv_ErrorFrom").Value
objEmail.To = DTSGlobalVariables("gv_ErrorTo").Value
objEmail.Subject = strErrorSubject
objEmail.Textbody = strErrorbody
objEmail.Send
Main = DTSTaskExecResult_Failure
End If
End Function
