Hi, I'm trying to migrate the SQL2000 DTS package into SQL 2005 SSIS package using migration wizard. Where the migrated activex script failed with the below error. Please advice. SSIS package "DTS_BNR_CPS_LOADFEED_Daily (1).dtsx" starting. Error: 0xC0048006 at Script to chk size of ctl file, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x0204A7EC. Task failed: Script to chk size of ctl file SSIS package "DTS_BNR_CPS_LOADFEED_Daily (1).dtsx" finished: Success. It;s working fine on SQL2000 DTS package.

Check this previous thread: http://social.msdn.microsoft.com/Forums/sr-Latn-CS/sqlintegrationservices/thread/8f43a6e9-b0e9-4113-af9f-44a3363e4a11 Thanks. Liu An - MSFT

Hi, i;m not sure where to put my function main(). please advice. My script below.. Function Main() result = DTSTaskExecResult_Failure Dim strFeedFilesPath Dim CurrentDate CurrentDate = Date DTSGlobalVariables("G_SP_VALUE").Value = 0 DTSGlobalVariables("G_SET_LOOPCTL").Value = True ResetGlobalVariables() IF DTSGlobalVariables("G_START_JOBTIME").Value = 0 THEN SetJobStartAndEndTime() END IF strFeedFilesPath = DTSGlobalVariables("G_CTL_FOLDERPATH").Value 'Files which are older than 120 days will be deleted RemoveArchiveFailedDuplicateFiles(DTSGlobalVariables("G_ARCHIVEPATH").Value) RemoveArchiveFailedDuplicateFiles(DTSGlobalVariables("G_DUPLICATEPATH").Value) RemoveArchiveFailedDuplicateFiles(DTSGlobalVariables("G_FAILEDPATH").Value) 'Function to proceed the DTS package IF DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 THEN IF FolderContainsCTLFile(strFeedFilesPath) = True THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN GetMoveCTLFile END IF GetCTLFile IF DTSGlobalVariables("G_CTL_FILEPATH").Value <> "" THEN GetCTLTimeStamp(DTSGlobalVariables("G_CTL_FILEPATH").Value) END IF END IF IF CanProceedDTSJob() THEN IF DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN GetMoveCTLFile END IF GetCTLFile DO WHILE GetUpdateFeedFileStatus(strFeedFilesPath) = True IF FolderContainsCTLFile(strFeedFilesPath) = True THEN GetCTLFile ELSE DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 EXIT DO END IF LOOP END IF END IF END IF IF DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 THEN IF DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 THEN ' Move .CTL file to Main folder Dim objFile SET objFile = CreateObject("Scripting.FileSystemObject") IF IsFolderEmpty(DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value) = False THEN objFile.MoveFile DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value & "\" & "*.CTL", DTSGlobalVariables("G_CTL_FOLDERPATH").Value END IF DTSGlobalVariables("G_CTL_FILENAME").Value = null set objFile=Nothing END IF END IF IF DTSGlobalVariables("G_RESET_STATUS").Value = 1 THEN DTSGlobalVariables("G_CTL_FILENAME").Value = null END IF IF isnull(DTSGlobalVariables("G_CTL_FILENAME").Value) THEN DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 0 DTSGlobalVariables("G_RESET_STATUS").Value = 0 result = DTSTaskExecResult_Failure ELSE IF FolderContainsCTLFile(strFeedFilesPath) = True THEN result = DTSTaskExecResult_Success ELSE DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 0 DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_RESET_STATUS").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CTL_FILENAME").Value = null ' result = DTSTaskExecResult_Failure END IF END IF Main = result End Function Function WaitForCTLFeedFile(strFolderPath) ' Wait to get .CTL file in the folder Dim objDBCon,strCon,strSQLQuery, strSQLJobTime,strSQLJobmin Dim strStartTime, strEndTime, strSQLHourQuery, strJobHour, strNIPSQL, objRSNIP, strCompleteSQL, objRSCOMPLETE,strSQLMinQuery, strJobMin Dim strStartmin, strEndmin CurrentDate = Date set objDBCon = CreateObject("ADODB.Connection") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon set objRecordset = CreateObject("ADODB.Recordset") Set objRSCOMPLETE = CreateObject("ADODB.Recordset") Set objRSNIP = CreateObject("ADODB.Recordset") Set objRSError = CreateObject("ADODB.Recordset") strSQLDQuery = " select Count(1) As 'DailyCount' from Tbl_FeedFile Where Frequency = 'D' " set objRecordset = objDBCon.Execute(strSQLDQuery) IF NOT objRecordset.EOF THEN strDailyCount = objRecordset("DailyCount") END IF strSQLMQuery = " select Count(1) As 'MonthlyCount' from Tbl_FeedFile Where Frequency = 'M' " set objRecordset = objDBCon.Execute(strSQLMQuery) IF NOT objRecordset.EOF THEN strMonthlyCount = objRecordset("MonthlyCount") END IF strSQLJobTime = " Select DatePart(hh, Job_StartTime) As Job_StartTime, DatePart(hh, Job_EndTime) As Job_EndTime From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed'" ' strSQLJobTime = " Select DatePart(n, Job_StartTime) As Job_StartTime, DatePart(n, Job_EndTime) As Job_EndTime From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed'" set objRecordset = objDBCon.Execute(strSQLJobTime) IF NOT objRecordset.EOF Then strStartTime = objRecordset("Job_StartTime") strEndTime = objRecordset("Job_EndTime") END IF strSQLJobmin = " Select DatePart(n, Job_StartTime) As Job_Startmin, DatePart(n, Job_EndTime) As Job_Endmin From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed'" set objRecordset = objDBCon.Execute(strSQLJobmin) IF NOT objRecordset.EOF Then strEndmin = objRecordset("Job_Endmin") END IF ' strJobHour =GetEndTime(objDBCon) ' strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN 'PROCESS MONTH END AND DAILY FEEDFILES [FREQUENCY) strSQLQuery = " SELECT Top 1 'NIPFileExists' FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strSQLQuery = strSQLQuery + " WHERE TF.File_Status = 'NIP' AND LC.Status LIKE RTRIM(LTRIM('READY%')) AND TF.Frequency = 'M' " set objRecordset = objDBCon.Execute(strSQLQuery) IF objRecordset.EOF THEN strSQLQuery = " select File_Status from Tbl_FeedFile Where File_Status = 'NIP' AND Frequency = 'M' " set objRecordset = objDBCon.Execute(strSQLQuery) END IF strSQLQuery = " select File_Status from Tbl_FeedFile Where File_Status = 'E' And File_Status <> 'NIP' AND Frequency = 'M' " set objRSError = objDBCon.Execute(strSQLQuery) strNIPSQL = " SELECT TF.File_Status FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strNIPSQL = strNIPSQL + " WHERE TF.File_Status = 'NIP' AND LC.Status= 'COMPLETE' AND TF.Frequency = 'M' " set objRSNIP = objDBCon.Execute(strNIPSQL) strCompleteSQL = " SELECT TF.File_Status FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strCompleteSQL = strCompleteSQL + " WHERE TF.File_Status = 'C' AND LC.Status= 'COMPLETE' AND TF.Frequency = 'M' " set objRSCOMPLETE = objDBCon.Execute(strCompleteSQL) Else 'MSGBOX "PROCESS MONTH END FEEDFILES [ FREQUENCY]" strSQLQuery = " SELECT Top 1 'NIPFileExists' FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strSQLQuery = strSQLQuery + " WHERE TF.File_Status = 'NIP' AND LC.Status LIKE RTRIM(LTRIM('READY%')) AND TF.Frequency = 'D' " set objRecordset = objDBCon.Execute(strSQLQuery) IF objRecordset.EOF THEN strSQLQuery = " select File_Status As 'Completed' from Tbl_FeedFile Where File_Status = 'NIP' AND Frequency = 'D' " set objRecordset = objDBCon.Execute(strSQLQuery) END IF strSQLQuery = " SELECT Top 1 'ERRORRECORDS' As ERRORRECORDS FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name " strSQLQuery = strSQLQuery + " AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strSQLQuery = strSQLQuery + " WHERE TF.File_Status In ('E', 'C') AND ( LC.Status = 'COMPLETE' OR LC.Status = 'ERROR' ) AND TF.Frequency = 'D' And File_Status = 'NIP' " set objRSError = objDBCon.Execute(strSQLQuery) IF objRSError.EOF THEN strSQLQuery = " select File_Status As 'Completed' from Tbl_FeedFile Where File_Status = 'E' And File_Status <> 'NIP' AND Frequency = 'D' " set objRSError = objDBCon.Execute(strSQLQuery) END IF strNIPSQL = " SELECT Distinct TF.File_Status As 'Completed' FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strNIPSQL = strNIPSQL + " WHERE TF.File_Status = 'NIP' AND LC.Status= 'COMPLETE' AND TF.Frequency = 'D' " set objRSNIP = objDBCon.Execute(strNIPSQL) strCompleteSQL = " SELECT Distinct TF.File_Status As 'Completed' FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strCompleteSQL = strCompleteSQL + " WHERE TF.File_Status = 'C' AND LC.Status= 'COMPLETE' AND TF.Frequency = 'D' " set objRSCOMPLETE = objDBCon.Execute(strCompleteSQL) End If IF NOT objRecordset.EOF Then DO WHILE strJobHour <= strEndTime IF FolderContainsCTLFile(strFolderPath) = False THEN IF (strJobHour = strEndTime and strJobmin = strEndmin) THEN AddFileNotArrivedStatus(objDBCon) EXIT DO END IF WaitForCTLFeedFile = False ELSE ' DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = True EXIT DO END IF 'strJobHour =GetEndTime(objDBCon) 'strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin LOOP ELSEIF NOT objRSError.EOF THEN DO WHILE strJobHour <= strEndTime IF FolderContainsCTLFile(strFolderPath) = False THEN IF DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 THEN DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = False EXIT DO END IF WaitForCTLFeedFile = False EXIT DO IF (strJobHour = strEndTime and strJobmin = strEndmin) THEN AddFileNotArrivedStatus(objDBCon) EXIT DO END IF ELSE WaitForCTLFeedFile = True EXIT DO END IF 'strJobHour =GetEndTime(objDBCon) 'strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin LOOP ELSEIF NOT objRSNIP.EOF THEN DO WHILE strJobHour <= strEndTime IF FolderContainsCTLFile(strFolderPath) = False THEN WaitForCTLFeedFile = False IF DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 THEN DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = False EXIT DO END IF IF (strJobHour = strEndTime and strJobmin = strEndmin) THEN AddFileNotArrivedStatus(objDBCon) EXIT DO END IF ELSE ' DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = True EXIT DO END IF 'strJobHour =GetEndTime(objDBCon) 'strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin LOOP ELSEIF NOT objRSCOMPLETE.EOF THEN DO WHILE strJobHour <= strEndTime IF FolderContainsCTLFile(strFolderPath) = False THEN WaitForCTLFeedFile = False IF (strJobHour = strEndTime and strJobmin = strEndmin) THEN AddFileNotArrivedStatus(objDBCon) EXIT DO END IF ELSE IF DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 THEN DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = False EXIT DO END IF ' DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = True EXIT DO END IF 'strJobHour =GetEndTime(objDBCon) 'strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin LOOP ELSE WaitForCTLFeedFile = False END IF objRecordset.Close objRSNIP.Close objRSError.Close objDBCon.Close Set objDBCon = Nothing End Function Function FolderContainsCTLFile(strFolderPath) ' Folder have .CTL file in the folder Dim oFileObject,oFolder SET oFileObject = CreateObject("Scripting.FileSystemObject") strFile=null SET oFolder = oFileObject.GetFolder(strFolderPath) SET oFolderFiles = oFolder.Files FolderContainsCTLFile = False DTSGlobalVariables("G_CTL_FILENAME").Value = null FOR EACH oFile IN oFolderFiles strFile = Mid ( oFile, InstrRev(oFile,"\")+1,Len(oFile) - InstrRev(oFile,"\")) strFullFileName = strFile if InStr(strFile, ".CTL") > 0 then FolderContainsCTLFile = True Exit For End If NEXT set oFileObject = Nothing End Function Function GetCTLFile() ' Get the first CTL file Dim oFileObject,oFolder,iFileCount ,strFile,strFullFileName,iFeedFormat SET oFileObject = CreateObject("Scripting.FileSystemObject") DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_DB_DAT_FILENAME").Value =null strFile=null SET oFolder = oFileObject.GetFolder(DTSGlobalVariables("G_CTL_FOLDERPATH").Value) SET oFolderFiles = oFolder.Files iFileCount =0 'Get files from Feedfiles folder FOR EACH oFile IN oFolderFiles strFile = Mid ( oFile, InstrRev(oFile,"\")+1,Len(oFile) - InstrRev(oFile,"\")) strFullFileName = Mid(strFile,1, InstrRev(strFile,".")-1) IF instr(strFile, ".CTL") > 0 THEN IF(StrComp( strFile , "DEF7.CTL",vbtextcompare ) = 0) Then SetGlobalValue() ELSE Call GetLineCountFromTheFile(oFile,oFile.Name) strFile = oFile.Name DTSGlobalVariables("G_CTL_FILENAME").Value = strFile SourceId = mid(strFile, 1, instr(strFile,".") - 1) ' DTSGlobalVariables("G_SOURCEID").Value = SourceId IF oFile.size > 0 THEN DTSGlobalVariables("G_CTL_FILEPATH").Value= oFile END IF END IF EXIT FOR END IF NEXT End function Function GetUpdateFeedFileStatus(strFolderPath) 'msgbox " GetUpdateFeedFileStatus " ' Update Tbl_FeedFile status Dim objDBCon, StrCon, objRecordset, strSQL, strFileStatus, strDataLoop,strDataFileName, strRSCount, strSQLCount, strRSDuplicate Dim oFSO, strSourceCTLFile, strSourceDATFile, strDestinationCTLFile, strDestinationDATFile, flag, strDate, strSourceId, strTableId, strDatCount Dim strCompletedDATCount, strDuplicateSourceId, strDuplicateTableId, strDuplicateStoredProcedure, strDuplicateFeedFileName, strDuplicateTimeStamp Dim strSQLComplete, strTotalSQL, strComplete, strTotal, strDatDuplicateCount CurrentDate = Date set oFSO = CreateObject("Scripting.FileSystemObject") set objDBCon = CreateObject("ADODB.Connection") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon set objRecordset = CreateObject("ADODB.Recordset") set strRSCount = CreateObject("ADODB.Recordset") set strRSDuplicate = CreateObject("ADODB.Recordset") IF isnull(DTSGlobalVariables("G_SOURCEID").Value) =False THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQL = "select TOP 1 'Error Exists' As File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' AND Frequency = 'M' " ELSE IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = "select TOP 1 'Error Exists' As File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " ELSE strSQL = "select TOP 1 'Error Exists' As File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' AND Frequency = 'D' " END IF END IF set objRecordset = objDBCon.Execute(strSQL) IF NOT objRecordset.EOF THEN IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = " DELETE FROM Load_Control WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Convert(DateTime, Convert(VarChar(10), MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 101)) AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' " set objRecordset = objDBCon.Execute(strSQL) ELSE strSQL = " DELETE FROM Load_Control WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Convert(DateTime, Convert(VarChar(10), MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 101)) " set objRecordset = objDBCon.Execute(strSQL) END IF IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = " Update Tbl_FeedFile Set File_Status = 'C', Task_Status = 'STORED PROCEDURE COMPLETED' WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'NIP' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' " set objRecordset = objDBCon.Execute(strSQL) strSQL = " Update Tbl_FeedFile Set File_Status = 'NIP', Task_Status = NULL WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' " set objRecordset = objDBCon.Execute(strSQL) ELSE strSQL = " Update Tbl_FeedFile Set File_Status = 'C', Task_Status = 'STORED PROCEDURE COMPLETED' WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'NIP' " set objRecordset = objDBCon.Execute(strSQL) strSQL = " Update Tbl_FeedFile Set File_Status = 'NIP', Task_Status = NULL WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' " set objRecordset = objDBCon.Execute(strSQL) END IF END IF GetCTLTimeStamp(DTSGlobalVariables("G_CTL_FILEPATH").Value) ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN 'PROCESS MONTH END AND DAILY FEEDFILES [FREQUENCY) strSQLComplete = " SELECT Count(*) As 'Completed' FROM Tbl_FeedFile TF Inner Join Load_Control LC ON LC.Source_ID = TF.Source_ID AND LC.Table_ID = TF.Table_ID AND LC.File_Name = TF.FeedFile_Name " strSQLComplete = strSQLComplete + " AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) WHERE LC.Status = 'COMPLETE' AND TF.File_Status = 'NIP' AND TF.Frequency = 'M' " set objRecordset = objDBCon.Execute(strSQLComplete) Else 'MSGBOX "PROCESS MONTH END FEEDFILES [ FREQUENCY]" strSQLComplete = " SELECT Count(*) As 'Completed' FROM Tbl_FeedFile TF Inner Join Load_Control LC ON LC.Source_ID = TF.Source_ID AND LC.Table_ID = TF.Table_ID AND LC.File_Name = TF.FeedFile_Name " strSQLComplete = strSQLComplete + " AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) WHERE LC.Status = 'COMPLETE' AND TF.File_Status = 'NIP' AND TF.Frequency = 'D' " set objRecordset = objDBCon.Execute(strSQLComplete) End If IF NOT objRecordset.EOF THEN strComplete = objRecordset("Completed") ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN 'PROCESS MONTH END AND DAILY FEEDFILES [FREQUENCY) strTotalSQL = " SELECT Count(*) As 'TotalCount' FROM Tbl_FeedFile WHERE Frequency = 'M' " set objRecordset = objDBCon.Execute(strTotalSQL) Else 'MSGBOX "PROCESS MONTH END FEEDFILES [ FREQUENCY]" strTotalSQL = " SELECT Count(*) As 'TotalCount' FROM Tbl_FeedFile WHERE Frequency = 'D' " set objRecordset = objDBCon.Execute(strTotalSQL) End If IF NOT objRecordset.EOF THEN strTotal = objRecordset("TotalCount") END IF END IF IF strComplete = strTotal THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN IF DTSGlobalVariables("G_SET_LOOPCTL").Value = True THEN GetMoveCTLFile END IF END IF IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQL = "select Source_Id, FeedFile_Name from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'M' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'M' " ELSE IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = "select Source_Id, FeedFile_Name from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " ELSE strSQL = "select Source_Id, FeedFile_Name from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'D' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'D' " END IF END IF set objRecordset = objDBCon.Execute(strSQL) set strRSCount = objDBCon.Execute(strSQLCount) IF NOT strRSCount.EOF THEN strDatDuplicateCount = strRSCount("Count") END IF flag = 0 strDataLoop = 1 While not objRecordset.EOF strDataFileName = objRecordset("FeedFile_Name") strSourceId = objRecordset("Source_id") strDate = Replace( FormatDateTime(Now(), vbGeneralDate),"/","") strDate = Replace( strDate," ","") strDate = Replace( strDate,":","") strDate = Left( strDate ,(Len(strDate)-2) ) IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN If Instr(strDataFileName, ".DAT") > 0 Then strSourceCTLFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & Mid(strDataFileName,1, InstrRev(strDataFileName,".")-1) & ".CTL" strDestinationCTLFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & Mid(strDataFileName,1, InstrRev(strDataFileName,".")-1) & ".CTL" & "_" & strDate END IF ELSE strSourceCTLFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & strSourceId & ".CTL" strDestinationCTLFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & strSourceId & ".CTL" & "_" & strDate END IF strSourceDATFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & strDataFileName strDestinationDATFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & strDataFileName & "_" & strDate strSQL = "select TOP 1 Source_Id, Table_Id, StoredProcedure_Name, FeedFile_Name, Mf_TimeStamp from Tbl_ErrorLog where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' And FeedFile_Name = '"& strDataFileName &"' AND Status = 'STORED PROCEDURE COMPLETED' AND Convert(DateTime, Convert(VarChar(10), MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 101)) " set strRSDuplicate = objDBCon.Execute(strSQL) IF NOT strRSDuplicate.EOF THEN strDuplicateSourceId = strRSDuplicate("Source_id") strDuplicateTableId = strRSDuplicate("Table_id") strDuplicateStoredProcedure = strRSDuplicate("StoredProcedure_Name") strDuplicateFeedFileName = strRSDuplicate("FeedFile_Name") strDuplicateTimeStamp = strRSDuplicate("Mf_TimeStamp") END IF strRSDuplicate.Close IF oFSO.FileExists(strSourceDATFile) THEN oFSO.MoveFile strSourceDATFile, strDestinationDATFile flag = flag + 1 UpdateFeedFile strDuplicateSourceId, strDuplicateTableId, strDuplicateStoredProcedure, strDuplicateFeedFileName, strDuplicateTimeStamp END IF If ( strDatDuplicateCount = flag ) then oFSO.MoveFile strSourceCTLFile, strDestinationCTLFile DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_SOURCEID").Value =null End if strDataLoop = strDataLoop + 1 objRecordset.Movenext WEND GetUpdateFeedFileStatus = True ELSE IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQL = "select Source_Id, Table_Id, FeedFile_Name, File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND Frequency = 'M' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'M' " ELSE IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = "select Source_Id, Table_Id, FeedFile_Name, File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " ELSE strSQL = "select Source_Id, Table_Id, FeedFile_Name, File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND Frequency = 'D' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'D' " END IF END IF set objRecordset = objDBCon.Execute(strSQL) set strRSCount = objDBCon.Execute(strSQLCount) strDatCount = strRSCount("Count") flag = 0 strDataLoop = 1 While not objRecordset.EOF strFileStatus = objRecordset("File_Status") strDataFileName = objRecordset("FeedFile_Name") strSourceId = objRecordset("Source_id") strTableId = objRecordset("Table_id") DTSGlobalVariables("G_DB_DAT_FILENAME").Value = strDataFileName DTSGlobalVariables("G_SOURCEID").Value = strSourceId If strFileStatus = "NIP" Then strSQL = "Update Tbl_FeedFile Set File_Status = 'P', Task_Status = 'PROCESS' where source_id = '"& strSourceId &"' and FeedFile_Name = '"& strDataFileName &"'" objDBCon.Execute(strSQL) Elseif Trim(strFileStatus) = "C" Then strDate = Replace( FormatDateTime(Now(), vbGeneralDate),"/","") strDate = Replace( strDate," ","") strDate = Replace( strDate,":","") strDate = Left( strDate ,(Len(strDate)-2) ) IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN If Instr(strDataFileName, ".DAT") > 0 Then strSourceCTLFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & Mid(strDataFileName,1, InstrRev(strDataFileName,".")-1) & ".CTL" strDestinationCTLFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & Mid(strDataFileName,1, InstrRev(strDataFileName,".")-1) & ".CTL" & "_" & strDate END IF ELSE strSourceCTLFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & strSourceId & ".CTL" strDestinationCTLFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & strSourceId & ".CTL" & "_" & strDate END IF strSourceDATFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & strDataFileName strDestinationDATFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & strDataFileName & "_" & strDate strSQL = "select TOP 1 Source_Id, Table_Id, StoredProcedure_Name, FeedFile_Name, Mf_TimeStamp from Tbl_ErrorLog where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' And FeedFile_Name = '"& strDataFileName &"' AND Status = 'STORED PROCEDURE COMPLETED' AND Convert(DateTime, Convert(VarChar(10), MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 101)) " set strRSDuplicate = objDBCon.Execute(strSQL) IF NOT strRSDuplicate.EOF THEN strDuplicateSourceId = strRSDuplicate("Source_id") strDuplicateTableId = strRSDuplicate("Table_id") strDuplicateStoredProcedure = strRSDuplicate("StoredProcedure_Name") strDuplicateFeedFileName = strRSDuplicate("FeedFile_Name") strDuplicateTimeStamp = strRSDuplicate("Mf_TimeStamp") END IF strRSDuplicate.Close IF oFSO.FileExists(strSourceDATFile) THEN oFSO.MoveFile strSourceDATFile, strDestinationDATFile flag = flag + 1 UpdateFeedFile strDuplicateSourceId, strDuplicateTableId, strDuplicateStoredProcedure, strDuplicateFeedFileName, strDuplicateTimeStamp END IF End if strDataLoop = strDataLoop + 1 objRecordset.Movenext WEND IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' And File_Status = 'C' AND Frequency = 'M' " ELSE IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' And File_Status = 'C' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " ELSE strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' And File_Status = 'C' AND Frequency = 'D' " END IF END IF set strRSCount = objDBCon.Execute(strSQLCount) strCompletedDATCount = strRSCount("Count") IF strDatCount <> 0 AND strCompletedDATCount <> 0 THEN If ( strDatCount = strCompletedDATCount ) then oFSO.MoveFile strSourceCTLFile, strDestinationCTLFile DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_SOURCEID").Value =null DTSGlobalVariables("G_SET_UPDATE_STATUS").Value = 1 GetUpdateFeedFileStatus = True Else GetUpdateFeedFileStatus = False End if END IF ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN 'PROCESS MONTH END AND DAILY FEEDFILES [FREQUENCY) strQuery = " Select Count(*) As Total From Tbl_FeedFile WHERE Frequency = 'M' " set Recordset = objDBCon.Execute(strQuery) IF NOT Recordset.EOF THEN strTotalRecord = Recordset("Total") END IF strQuery = " Select Count(1) As Completed From Tbl_FeedFile Where File_Status = 'C' AND Frequency = 'M' " set Recordset = objDBCon.Execute(strQuery) IF NOT Recordset.EOF THEN IF Recordset("Completed") = strTotalRecord THEN strQuery = "Update Tbl_FeedFile Set File_Status = 'NIP', Task_Status = Null WHERE File_Status = 'C' AND Frequency = 'M' " set Recordset = objDBCon.Execute(strQuery) strQuery = "Update Tbl_FeedFile Set MF_TimeStamp = Null WHERE Frequency = 'M' " set Recordset = objDBCon.Execute(strQuery) DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 DTSGlobalVariables("G_CTL_FILENAME").Value = null END IF END IF ELSE strQuery = " Select Count(*) As Total From Tbl_FeedFile WHERE Frequency = 'D'" set Recordset = objDBCon.Execute(strQuery) IF NOT Recordset.EOF THEN strTotalRecord = Recordset("Total") END IF strQuery = " Select Count(1) As Completed From Tbl_FeedFile Where File_Status = 'C' AND Frequency = 'D' " set Recordset = objDBCon.Execute(strQuery) IF NOT Recordset.EOF THEN IF Recordset("Completed") = strTotalRecord THEN strQuery = "Update Tbl_FeedFile Set File_Status = 'NIP', Task_Status = Null WHERE File_Status = 'C' AND Frequency = 'D' " set Recordset = objDBCon.Execute(strQuery) strQuery = "Update Tbl_FeedFile Set MF_TimeStamp = Null WHERE Frequency = 'D' " set Recordset = objDBCon.Execute(strQuery) DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 DTSGlobalVariables("G_CTL_FILENAME").Value = null END IF END IF END IF END IF END IF End Function Function UpdateFeedFile(getSourceId, getTableId, getStoredProcedure, getFeedFileName, getTimestamp) ' To update Tbl_ErrorLog and Tbl_FeedFile status Dim objDBCon, strCon, strUpdateSQL, strInsertSQL set objDBCon = CreateObject("ADODB.Connection") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon strInsertSQL = " Insert Into Tbl_ErrorLog (Source_ID, Table_ID, StoredProcedure_Name, FeedFile_Name, Mf_TimeStamp, Error_Code, Status, Return_Status, Feed_ProcessTime) " strInsertSQL = strInsertSQL + " Values ( '" & getSourceId &"', '" & getTableId &"', '" & getStoredProcedure &"', '" & getFeedFileName & "', Convert(DateTime, '" & getTimestamp &"', 101), 5, 'DUPLICATE FILE', 1 , Convert(DateTime, '" & DTSGlobalVariables("G_SET_PROCESSTIME").Value & "', 101) ) " objDBCon.Execute(strInsertSQL) objDBCon.Close Set objDBCon = Nothing End Function Function SetJobStartAndEndTime() IF DTSGlobalVariables("G_START_JOBTIME").Value = 0 THEN ' To get and set job start time and total job time Dim objDBCon, objRS, strJobTime set objDBCon = CreateObject("ADODB.Connection") set objRS = CreateObject("ADODB.Recordset") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon strQuery = " Select JobTime From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed' " Set objRS = objDBCon.Execute( strQuery) IF NOT objRS.EOF THEN strJobTime = objRS("JobTime") strQuery = " Update Tbl_ConfigDTS Set Job_StartTime = GetDate(), Job_EndTime = DateAdd(hh, "& strJobTime &", GetDate()) Where DTSName = 'DTS_BNR_CPS_LoadFeed' " ' strQuery = " Update Tbl_ConfigDTS Set Job_StartTime = GetDate(), Job_EndTime = DateAdd(n, 5, GetDate()) Where DTSName = 'DTS_BNR_CPS_LoadFeed' " objDBCon.Execute (strQuery) strQuery = " Select Job_StartTime From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed' " Set objRS = objDBCon.Execute( strQuery) IF NOT objRS.EOF THEN DTSGlobalVariables("G_SET_PROCESSTIME").Value = objRS("Job_StartTime") END IF DTSGlobalVariables("G_START_JOBTIME").Value = 1 END IF objRS.Close objDBCon.Close Set objDBCon = Nothing END IF End Function Function CanProceedDTSJob() ' Function to proceed the DTS package Dim objDBCon,strCon,strSQLQuery IF WaitForCTLFeedFile(DTSGlobalVariables("G_CTL_FOLDERPATH").Value) = True Then 'MsgBox " FeedFile Yet to arrive. You can proceed the DTS Job To RUN..." CanProceedDTSJob = True ELSE 'MsgBox "All the feed files have arrived stop the Job. Job is running for more than two hours" CanProceedDTSJob = False End IF End Function Function FolderContainsCTLFile(strFolderPath) ' To check CTL file in the folder Dim oFileObject,oFolder SET oFileObject = CreateObject("Scripting.FileSystemObject") strFile=null SET oFolder = oFileObject.GetFolder(strFolderPath) SET oFolderFiles = oFolder.Files FolderContainsCTLFile = False FOR EACH oFile IN oFolderFiles strFile = Mid ( oFile, InstrRev(oFile,"\")+1,Len(oFile) - InstrRev(oFile,"\")) strFullFileName = strFile if InStr(strFile, ".CTL") > 0 then FolderContainsCTLFile = True Exit For End If NEXT set oFileObject = Nothing End Function Function GetCTLTimeStamp(strFile) Dim strTimeStamp IF InStr(strFile, strSearch) THEN Dim strNextLine Const ForReading = 1 Set objFSO = CreateObject("Scripting.FileSystemObject") IF objFSO.FileExists(strFile) THEN Set objTextFile = objFSO.OpenTextFile (strFile, 1) Do Until objTextFile.AtEndOfStream strNextLine = objTextFile.Readline arrCTLLINE = Split(strNextLine , vbTab) IF IsDate( arrCTLLINE(2)) THEN strTimeStamp = arrCTLLINE(2) END IF Loop DTSGlobalVariables("G_CTL_TIMESTAMP").Value = strTimeStamp DTSGlobalVariables("G_DAT_TIMESTAMP").Value = strTimeStamp END IF Set objFSO = nothing Set objTextFile = nothing END IF End Function Function FindNextMonthFirstDate(CurrentDate) 'Finding Next Month First Date Dim NextMonthsFirstDay,NextMonth,NextMonthYear NextMonthsFirstDay = "01" NextMonth = Month(CurrentDate) NextMonthYear = Int(Year(CurrentDate)) If NextMonth < 12 then NextMonth = NextMonth + 1 Else NextMonth = 1 NextMonthYear = NextMonthYear + 1 End If FindNextMonthFirstDate = DateSerial( NextMonthYear , NextMonth , NextMonthsFirstDay ) ' FindNextMonthFirstDate = CDate(NextMonth & "/" & NextMonthsFirstDay & "/" & NextMonthYear) End Function 'Function GetEndTime(objDBCon) Function GetEndTime Dim objDBConHr, strConHr Set objDBConHr= Createobject("ADODB.Connection") strConHr = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBConHr.Open strConHr strSQLHourQuery = " Select DatePart(hh, GetDate()) As JobHour " set objRecordset = objDBConHr.Execute(strSQLHourQuery) IF NOT objRecordset.EOF Then strJobHour = objRecordset("JobHour") END IF GetEndTime = strJobHour objDBConHr.Close Set objDBConHr = Nothing End Function 'Function GetEndmin(objDBCon) Function GetEndmin Dim objDBConMn, strConMn Set objDBConMn= Createobject("ADODB.Connection") strConMn = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBConMn.Open strConMn strSQLMinQuery = " Select DatePart(n, GetDate()) As JobMin" set objRecordset = objDBConMn.Execute(strSQLMinQuery) IF NOT objRecordset.EOF Then strJobMin = objRecordset("JobMin") END IF GetEndmin = strJobMin objDBConMn.Close Set objDBConMn = Nothing End Function Function AddFileNotArrivedStatus(objDBCon) CurrentDate = Date ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQLNIP = " Select Source_Id, Table_Id, FeedFile_Name From Tbl_FeedFile Where File_Status = 'NIP' And Frequency = 'M' " set Recordset = objDBCon.Execute(strSQLNIP) DO WHILE NOT Recordset.EOF strInsertSQL = " Insert Into Tbl_ErrorLog (Source_ID, Table_ID, FeedFile_Name, Mf_TimeStamp, Error_Code, Status, Return_Status, Feed_ProcessTime) " strInsertSQL = strInsertSQL + " Values ( '" & Recordset("Source_Id") &"', '" & Recordset("Table_Id") &"', '" & Recordset("FeedFile_Name") & "', Convert(DateTime, '" & DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 90, 'FILE NOT ARRIVED TO THE FEEDFILES FOLDER', 1, Convert(DateTime, '" & DTSGlobalVariables("G_SET_PROCESSTIME").Value & "', 101) ) " objDBCon.Execute(strInsertSQL) Recordset.MoveNext LOOP DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 DTSGlobalVariables("G_CTL_FILENAME").Value = null ELSE strSQLNIP = " Select Source_Id, Table_Id, FeedFile_Name, Mf_TimeStamp From Tbl_FeedFile Where File_Status = 'NIP' And Frequency = 'D' " set Recordset = objDBCon.Execute(strSQLNIP) DO WHILE NOT Recordset.EOF strInsertSQL = " Insert Into Tbl_ErrorLog (Source_ID, Table_ID, FeedFile_Name, Mf_TimeStamp, Error_Code, Status, Return_Status, Feed_ProcessTime) " strInsertSQL = strInsertSQL + " Values ( '" & Recordset("Source_Id") &"', '" & Recordset("Table_Id") &"', '" & Recordset("FeedFile_Name") & "', Convert(DateTime, '" & DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 90, 'FILE NOT ARRIVED TO THE FEEDFILES FOLDER', 1, Convert(DateTime, '" & DTSGlobalVariables("G_SET_PROCESSTIME").Value & "', 101) ) " objDBCon.Execute(strInsertSQL) Recordset.MoveNext LOOP DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 DTSGlobalVariables("G_CTL_FILENAME").Value = null END IF End Function Function GetLineCountFromTheFile(strPath_FileName,Ctlfilename) Dim objDBCon,strCon,strSQLQuery,FileLineCount,arrCtlLine,Strdatfile Dim FSO, file, TextStream, Line Const ForReading = 1 Const TristateUseDefault = -2 FileLineCount = 0 set FSO = createObject("Scripting.FileSystemObject") set file = FSO.GetFile(strPath_FileName) Set TextStream = file.OpenAsTextStream(ForReading, TristateUseDefault) ' Read the file line by line Do While Not TextStream.AtEndOfStream Line = TextStream.readline arrCtlLine = Split(Line , vbTab) IF arrCtlLine(0) <> "" AND arrCtlLine(1) <> "" AND arrCtlLine(4) <> "" THEN DTSGlobalVariables("G_SOURCEID").Value = arrCtlLine(0) DTSGlobalVariables("G_DAT_TABLENAME").Value = arrCtlLine(1) DTSGlobalVariables("G_SET_FEEDFILENAME").Value = arrCtlLine(4) END IF Loop End Function Function FindLastWorkingDayOfTheMonth(CurrentDate) Dim LastDayofTheMonth LastDayofTheMonth = FindLastDateOfTheMonth(CurrentDate) Select Case WeekDay(LastDayofTheMonth) Case 1 FindLastWorkingDayofTheMonth = DateAdd("d",LastDayofTheMonth,-2) Case 7 FindLastWorkingDayofTheMonth = DateAdd("d",LastDayofTheMonth,-1) Case Else FindLastWorkingDayofTheMonth = LastDayofTheMonth End Select End Function Function FindLastDateOfTheMonth(CurrentDate) FindLastDateOfTheMonth = DateAdd("d",FindNextMonthFirstDate(CurrentDate),-1) End Function Function ResetGlobalVariables() Dim objDBCon, objRS, strJobTime set objDBCon = CreateObject("ADODB.Connection") set objRS = CreateObject("ADODB.Recordset") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon strQuery = " Select Top 1 'ErrorOccured' As 'ErrorOccured' From Tbl_ConfigDTS Where DTSName = 'SetGlobalValue' " Set objRS = objDBCon.Execute( strQuery) IF NOT objRS.EOF THEN DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 DTSGlobalVariables("G_START_JOBTIME").Value = 0 strQuery = " Delete From Tbl_ConfigDTS Where DTSName = 'SetGlobalValue' " objDBCon.Execute( strQuery) MoveDailyCTLToMainFolder() END IF objRS.Close objDBCon.Close Set objDBCon = Nothing End Function Function SetGlobalValue() SET oFileObject = CreateObject("Scripting.FileSystemObject") IF oFileObject.FileExists( DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\DEF8.CTL") Then DTSGlobalVariables("G_CTL_FILEPATH").Value= DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\DEF8.CTL" DTSGlobalVariables("G_CTL_FILENAME").Value = "DEF8.CTL" DTSGlobalVariables("G_SOURCEID").Value = "DEF8" DTSGlobalVariables("G_DAT_TABLENAME").Value = "F202" Else DTSGlobalVariables("G_CTL_FILEPATH").Value= DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\DEF7.CTL" DTSGlobalVariables("G_CTL_FILENAME").Value = "DEF7.CTL" DTSGlobalVariables("G_SOURCEID").Value = "DEF7" DTSGlobalVariables("G_DAT_TABLENAME").Value = "F20B" End if End Function Function MoveCTLFeedFile(strSourceFolderPath_FileName,strDestinationFolderPath_FileName) ' Move .CTL file to respective folder Dim strExt,strDesPath,strSource,strDesFile, strDate Dim fso Set fso = CreateObject("Scripting.FileSystemObject") strDestinationFolderPath_FileName = strDestinationFolderPath_FileName SET objFile = CreateObject("Scripting.FileSystemObject") 'IF fso.fileexists(strSourceFolderPath_FileName) Then IF strSourceFolderPath_FileName <> "" AND strDestinationFolderPath_FileName <> "" THEN objFile.MoveFile strSourceFolderPath_FileName, strDestinationFolderPath_FileName END IF 'END IF set objFile=Nothing End Function Function GetMoveCTLFile() ' Get the first CTL file Dim oFileObject,oFolder,iFileCount ,strFile,strFullFileName,iFeedFormat Dim objDBCon, objRS set objDBCon = CreateObject("ADODB.Connection") set objRS = CreateObject("ADODB.Recordset") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon SET oFileObject = CreateObject("Scripting.FileSystemObject") DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_DB_DAT_FILENAME").Value =null strFile=null SET oFolder = oFileObject.GetFolder(DTSGlobalVariables("G_CTL_FOLDERPATH").Value) SET oFolderFiles = oFolder.Files iFileCount =0 'Get files from Feedfiles folder DO WHILE DTSGlobalVariables("G_SET_LOOPCTL").Value = True FOR EACH oFile IN oFolderFiles strFile = Mid ( oFile, InstrRev(oFile,"\")+1,Len(oFile) - InstrRev(oFile,"\")) strFullFileName = Mid(strFile,1, InstrRev(strFile,".")-1) IF instr(strFile, ".CTL") > 0 THEN Call GetLineCountFromTheFile(oFile,oFile.Name) IF DTSGlobalVariables("G_SOURCEID").Value <> "" AND DTSGlobalVariables("G_DAT_TABLENAME").Value <> "" AND DTSGlobalVariables("G_SET_FEEDFILENAME").Value <> "" THEN strQuery = " Select Top 1 Frequency From Tbl_FeedFile Where Source_ID = '" & DTSGlobalVariables("G_SOURCEID").Value & "' And Table_ID = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' And FeedFile_Name = '" & DTSGlobalVariables("G_SET_FEEDFILENAME").Value & "' AND Frequency <> 'D' " Set objRS = objDBCon.Execute( strQuery) DTSGlobalVariables("G_CTL_FILENAME").Value = oFile.Name IF NOT objRS.EOF THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN ' There is only Daily process so Move Month CTL Feed File to TempMonthly folder Call MoveCTLFeedFile(DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & DTSGlobalVariables("G_CTL_FILENAME").Value, DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value & "\" & DTSGlobalVariables("G_CTL_FILENAME").Value) END IF DTSGlobalVariables("G_SET_LOOPCTL").Value = True DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 ELSE DTSGlobalVariables("G_SET_LOOPCTL").Value = False EXIT DO END IF END IF EXIT FOR EXIT DO END IF NEXT IF WaitForCTLFeedFile(DTSGlobalVariables("G_CTL_FOLDERPATH").Value) = False THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 EXIT DO END IF END IF LOOP End function Function MoveTempToMain(strSourceFolderPath_FileName,strDestinationFolderPath_FileName) ' Move .CTL file to Main folder Dim strExt,strDesPath,strSource,strDesFile, strDate Dim fso Set fso = CreateObject("Scripting.FileSystemObject") SET objFile = CreateObject("Scripting.FileSystemObject") objFile.MoveFile strSourceFolderPath_FileName, strDestinationFolderPath_FileName set objFile=Nothing End Function Function MoveCTLFeedFile1(strSourceFolderPath_FileName,strDestinationFolderPath_FileName) ' Move .CTL file to respective folder Dim strExt,strDesPath,strSource,strDesFile, strDate Dim fso Set fso = CreateObject("Scripting.FileSystemObject") strDestinationFolderPath_FileName = strDestinationFolderPath_FileName SET objFile = CreateObject("Scripting.FileSystemObject") 'IF fso.fileexists(strSourceFolderPath_FileName) Then IF strSourceFolderPath_FileName <> "" AND strDestinationFolderPath_FileName <> "" THEN objFile.MoveFile strSourceFolderPath_FileName, strDestinationFolderPath_FileName END IF 'END IF set objFile=Nothing End Function Function IsFolderEmpty(strFolderPath) Dim objFSO, folderStatus Set objFSO = CreateObject("Scripting.FileSystemObject") Set folderStatus = objFSO.GetFolder(strFolderPath) If folderStatus.Size=0 Then IsFolderEmpty = True Else IsFolderEmpty = False End If Set objFSO = Nothing End Function Function MoveDailyCTLToMainFolder() ' change this function to move monthly files when package abends. IF IsFolderEmpty(DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value) = False THEN Dim oFileObject, oFolder, oFolderFiles, objFile SET oFileObject = CreateObject("Scripting.FileSystemObject") SET oFolder = oFileObject.GetFolder( DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value ) SET oFolderFiles = oFolder.Files oFileObject.MoveFile DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value & "\" & "*.CTL", DTSGlobalVariables("G_CTL_FOLDERPATH").Value DTSGlobalVariables("G_CTL_FILENAME").Value = null Set oFileObject = Nothing END IF End Function Function RemoveArchiveFailedDuplicateFiles(folderpath) Dim oFileObject, oFolder, oFolderFiles Dim objFSO, days_diff SET oFileObject = CreateObject("Scripting.FileSystemObject") SET oFolder = oFileObject.GetFolder(folderpath) SET oFolderFiles = oFolder.Files FOR EACH oFile IN oFolderFiles days_diff = datediff("d",oFile.DateCreated,date) if days_diff >DTSGlobalVariables("G_DAY_TOREMOVE_FILES").Value then oFileObject.deletefile(oFile) end if Next End Function Thanks & Regards, Karthikeyan

Hi, i;m not sure where to put my function main(). please advice. My script below.. Function Main() result = DTSTaskExecResult_Failure Dim strFeedFilesPath Dim CurrentDate CurrentDate = Date DTSGlobalVariables("G_SP_VALUE").Value = 0 DTSGlobalVariables("G_SET_LOOPCTL").Value = True ResetGlobalVariables() IF DTSGlobalVariables("G_START_JOBTIME").Value = 0 THEN SetJobStartAndEndTime() END IF strFeedFilesPath = DTSGlobalVariables("G_CTL_FOLDERPATH").Value 'Files which are older than 120 days will be deleted RemoveArchiveFailedDuplicateFiles(DTSGlobalVariables("G_ARCHIVEPATH").Value) RemoveArchiveFailedDuplicateFiles(DTSGlobalVariables("G_DUPLICATEPATH").Value) RemoveArchiveFailedDuplicateFiles(DTSGlobalVariables("G_FAILEDPATH").Value) 'Function to proceed the DTS package IF DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 THEN IF FolderContainsCTLFile(strFeedFilesPath) = True THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN GetMoveCTLFile END IF GetCTLFile IF DTSGlobalVariables("G_CTL_FILEPATH").Value <> "" THEN GetCTLTimeStamp(DTSGlobalVariables("G_CTL_FILEPATH").Value) END IF END IF IF CanProceedDTSJob() THEN IF DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN GetMoveCTLFile END IF GetCTLFile DO WHILE GetUpdateFeedFileStatus(strFeedFilesPath) = True IF FolderContainsCTLFile(strFeedFilesPath) = True THEN GetCTLFile ELSE DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 EXIT DO END IF LOOP END IF END IF END IF IF DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 THEN IF DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 THEN ' Move .CTL file to Main folder Dim objFile SET objFile = CreateObject("Scripting.FileSystemObject") IF IsFolderEmpty(DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value) = False THEN objFile.MoveFile DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value & "\" & "*.CTL", DTSGlobalVariables("G_CTL_FOLDERPATH").Value END IF DTSGlobalVariables("G_CTL_FILENAME").Value = null set objFile=Nothing END IF END IF IF DTSGlobalVariables("G_RESET_STATUS").Value = 1 THEN DTSGlobalVariables("G_CTL_FILENAME").Value = null END IF IF isnull(DTSGlobalVariables("G_CTL_FILENAME").Value) THEN DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 0 DTSGlobalVariables("G_RESET_STATUS").Value = 0 result = DTSTaskExecResult_Failure ELSE IF FolderContainsCTLFile(strFeedFilesPath) = True THEN result = DTSTaskExecResult_Success ELSE DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 0 DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_RESET_STATUS").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CTL_FILENAME").Value = null ' result = DTSTaskExecResult_Failure END IF END IF Main = result End Function Function WaitForCTLFeedFile(strFolderPath) ' Wait to get .CTL file in the folder Dim objDBCon,strCon,strSQLQuery, strSQLJobTime,strSQLJobmin Dim strStartTime, strEndTime, strSQLHourQuery, strJobHour, strNIPSQL, objRSNIP, strCompleteSQL, objRSCOMPLETE,strSQLMinQuery, strJobMin Dim strStartmin, strEndmin CurrentDate = Date set objDBCon = CreateObject("ADODB.Connection") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon set objRecordset = CreateObject("ADODB.Recordset") Set objRSCOMPLETE = CreateObject("ADODB.Recordset") Set objRSNIP = CreateObject("ADODB.Recordset") Set objRSError = CreateObject("ADODB.Recordset") strSQLDQuery = " select Count(1) As 'DailyCount' from Tbl_FeedFile Where Frequency = 'D' " set objRecordset = objDBCon.Execute(strSQLDQuery) IF NOT objRecordset.EOF THEN strDailyCount = objRecordset("DailyCount") END IF strSQLMQuery = " select Count(1) As 'MonthlyCount' from Tbl_FeedFile Where Frequency = 'M' " set objRecordset = objDBCon.Execute(strSQLMQuery) IF NOT objRecordset.EOF THEN strMonthlyCount = objRecordset("MonthlyCount") END IF strSQLJobTime = " Select DatePart(hh, Job_StartTime) As Job_StartTime, DatePart(hh, Job_EndTime) As Job_EndTime From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed'" ' strSQLJobTime = " Select DatePart(n, Job_StartTime) As Job_StartTime, DatePart(n, Job_EndTime) As Job_EndTime From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed'" set objRecordset = objDBCon.Execute(strSQLJobTime) IF NOT objRecordset.EOF Then strStartTime = objRecordset("Job_StartTime") strEndTime = objRecordset("Job_EndTime") END IF strSQLJobmin = " Select DatePart(n, Job_StartTime) As Job_Startmin, DatePart(n, Job_EndTime) As Job_Endmin From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed'" set objRecordset = objDBCon.Execute(strSQLJobmin) IF NOT objRecordset.EOF Then strEndmin = objRecordset("Job_Endmin") END IF ' strJobHour =GetEndTime(objDBCon) ' strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN 'PROCESS MONTH END AND DAILY FEEDFILES [FREQUENCY) strSQLQuery = " SELECT Top 1 'NIPFileExists' FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strSQLQuery = strSQLQuery + " WHERE TF.File_Status = 'NIP' AND LC.Status LIKE RTRIM(LTRIM('READY%')) AND TF.Frequency = 'M' " set objRecordset = objDBCon.Execute(strSQLQuery) IF objRecordset.EOF THEN strSQLQuery = " select File_Status from Tbl_FeedFile Where File_Status = 'NIP' AND Frequency = 'M' " set objRecordset = objDBCon.Execute(strSQLQuery) END IF strSQLQuery = " select File_Status from Tbl_FeedFile Where File_Status = 'E' And File_Status <> 'NIP' AND Frequency = 'M' " set objRSError = objDBCon.Execute(strSQLQuery) strNIPSQL = " SELECT TF.File_Status FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strNIPSQL = strNIPSQL + " WHERE TF.File_Status = 'NIP' AND LC.Status= 'COMPLETE' AND TF.Frequency = 'M' " set objRSNIP = objDBCon.Execute(strNIPSQL) strCompleteSQL = " SELECT TF.File_Status FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strCompleteSQL = strCompleteSQL + " WHERE TF.File_Status = 'C' AND LC.Status= 'COMPLETE' AND TF.Frequency = 'M' " set objRSCOMPLETE = objDBCon.Execute(strCompleteSQL) Else 'MSGBOX "PROCESS MONTH END FEEDFILES [ FREQUENCY]" strSQLQuery = " SELECT Top 1 'NIPFileExists' FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strSQLQuery = strSQLQuery + " WHERE TF.File_Status = 'NIP' AND LC.Status LIKE RTRIM(LTRIM('READY%')) AND TF.Frequency = 'D' " set objRecordset = objDBCon.Execute(strSQLQuery) IF objRecordset.EOF THEN strSQLQuery = " select File_Status As 'Completed' from Tbl_FeedFile Where File_Status = 'NIP' AND Frequency = 'D' " set objRecordset = objDBCon.Execute(strSQLQuery) END IF strSQLQuery = " SELECT Top 1 'ERRORRECORDS' As ERRORRECORDS FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name " strSQLQuery = strSQLQuery + " AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strSQLQuery = strSQLQuery + " WHERE TF.File_Status In ('E', 'C') AND ( LC.Status = 'COMPLETE' OR LC.Status = 'ERROR' ) AND TF.Frequency = 'D' And File_Status = 'NIP' " set objRSError = objDBCon.Execute(strSQLQuery) IF objRSError.EOF THEN strSQLQuery = " select File_Status As 'Completed' from Tbl_FeedFile Where File_Status = 'E' And File_Status <> 'NIP' AND Frequency = 'D' " set objRSError = objDBCon.Execute(strSQLQuery) END IF strNIPSQL = " SELECT Distinct TF.File_Status As 'Completed' FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strNIPSQL = strNIPSQL + " WHERE TF.File_Status = 'NIP' AND LC.Status= 'COMPLETE' AND TF.Frequency = 'D' " set objRSNIP = objDBCon.Execute(strNIPSQL) strCompleteSQL = " SELECT Distinct TF.File_Status As 'Completed' FROM Load_Control LC INNER JOIN Tbl_FeedFile TF ON TF.Source_ID = LC.Source_ID AND TF.FeedFile_Name = LC.File_Name AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) " strCompleteSQL = strCompleteSQL + " WHERE TF.File_Status = 'C' AND LC.Status= 'COMPLETE' AND TF.Frequency = 'D' " set objRSCOMPLETE = objDBCon.Execute(strCompleteSQL) End If IF NOT objRecordset.EOF Then DO WHILE strJobHour <= strEndTime IF FolderContainsCTLFile(strFolderPath) = False THEN IF (strJobHour = strEndTime and strJobmin = strEndmin) THEN AddFileNotArrivedStatus(objDBCon) EXIT DO END IF WaitForCTLFeedFile = False ELSE ' DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = True EXIT DO END IF 'strJobHour =GetEndTime(objDBCon) 'strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin LOOP ELSEIF NOT objRSError.EOF THEN DO WHILE strJobHour <= strEndTime IF FolderContainsCTLFile(strFolderPath) = False THEN IF DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 THEN DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = False EXIT DO END IF WaitForCTLFeedFile = False EXIT DO IF (strJobHour = strEndTime and strJobmin = strEndmin) THEN AddFileNotArrivedStatus(objDBCon) EXIT DO END IF ELSE WaitForCTLFeedFile = True EXIT DO END IF 'strJobHour =GetEndTime(objDBCon) 'strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin LOOP ELSEIF NOT objRSNIP.EOF THEN DO WHILE strJobHour <= strEndTime IF FolderContainsCTLFile(strFolderPath) = False THEN WaitForCTLFeedFile = False IF DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 THEN DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = False EXIT DO END IF IF (strJobHour = strEndTime and strJobmin = strEndmin) THEN AddFileNotArrivedStatus(objDBCon) EXIT DO END IF ELSE ' DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = True EXIT DO END IF 'strJobHour =GetEndTime(objDBCon) 'strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin LOOP ELSEIF NOT objRSCOMPLETE.EOF THEN DO WHILE strJobHour <= strEndTime IF FolderContainsCTLFile(strFolderPath) = False THEN WaitForCTLFeedFile = False IF (strJobHour = strEndTime and strJobmin = strEndmin) THEN AddFileNotArrivedStatus(objDBCon) EXIT DO END IF ELSE IF DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 THEN DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = False EXIT DO END IF ' DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 WaitForCTLFeedFile = True EXIT DO END IF 'strJobHour =GetEndTime(objDBCon) 'strJobmin =GetEndmin(objDBCon) strJobHour =GetEndTime strJobmin =GetEndmin LOOP ELSE WaitForCTLFeedFile = False END IF objRecordset.Close objRSNIP.Close objRSError.Close objDBCon.Close Set objDBCon = Nothing End Function Function FolderContainsCTLFile(strFolderPath) ' Folder have .CTL file in the folder Dim oFileObject,oFolder SET oFileObject = CreateObject("Scripting.FileSystemObject") strFile=null SET oFolder = oFileObject.GetFolder(strFolderPath) SET oFolderFiles = oFolder.Files FolderContainsCTLFile = False DTSGlobalVariables("G_CTL_FILENAME").Value = null FOR EACH oFile IN oFolderFiles strFile = Mid ( oFile, InstrRev(oFile,"\")+1,Len(oFile) - InstrRev(oFile,"\")) strFullFileName = strFile if InStr(strFile, ".CTL") > 0 then FolderContainsCTLFile = True Exit For End If NEXT set oFileObject = Nothing End Function Function GetCTLFile() ' Get the first CTL file Dim oFileObject,oFolder,iFileCount ,strFile,strFullFileName,iFeedFormat SET oFileObject = CreateObject("Scripting.FileSystemObject") DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_DB_DAT_FILENAME").Value =null strFile=null SET oFolder = oFileObject.GetFolder(DTSGlobalVariables("G_CTL_FOLDERPATH").Value) SET oFolderFiles = oFolder.Files iFileCount =0 'Get files from Feedfiles folder FOR EACH oFile IN oFolderFiles strFile = Mid ( oFile, InstrRev(oFile,"\")+1,Len(oFile) - InstrRev(oFile,"\")) strFullFileName = Mid(strFile,1, InstrRev(strFile,".")-1) IF instr(strFile, ".CTL") > 0 THEN IF(StrComp( strFile , "DEF7.CTL",vbtextcompare ) = 0) Then SetGlobalValue() ELSE Call GetLineCountFromTheFile(oFile,oFile.Name) strFile = oFile.Name DTSGlobalVariables("G_CTL_FILENAME").Value = strFile SourceId = mid(strFile, 1, instr(strFile,".") - 1) ' DTSGlobalVariables("G_SOURCEID").Value = SourceId IF oFile.size > 0 THEN DTSGlobalVariables("G_CTL_FILEPATH").Value= oFile END IF END IF EXIT FOR END IF NEXT End function Function GetUpdateFeedFileStatus(strFolderPath) 'msgbox " GetUpdateFeedFileStatus " ' Update Tbl_FeedFile status Dim objDBCon, StrCon, objRecordset, strSQL, strFileStatus, strDataLoop,strDataFileName, strRSCount, strSQLCount, strRSDuplicate Dim oFSO, strSourceCTLFile, strSourceDATFile, strDestinationCTLFile, strDestinationDATFile, flag, strDate, strSourceId, strTableId, strDatCount Dim strCompletedDATCount, strDuplicateSourceId, strDuplicateTableId, strDuplicateStoredProcedure, strDuplicateFeedFileName, strDuplicateTimeStamp Dim strSQLComplete, strTotalSQL, strComplete, strTotal, strDatDuplicateCount CurrentDate = Date set oFSO = CreateObject("Scripting.FileSystemObject") set objDBCon = CreateObject("ADODB.Connection") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon set objRecordset = CreateObject("ADODB.Recordset") set strRSCount = CreateObject("ADODB.Recordset") set strRSDuplicate = CreateObject("ADODB.Recordset") IF isnull(DTSGlobalVariables("G_SOURCEID").Value) =False THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQL = "select TOP 1 'Error Exists' As File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' AND Frequency = 'M' " ELSE IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = "select TOP 1 'Error Exists' As File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " ELSE strSQL = "select TOP 1 'Error Exists' As File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' AND Frequency = 'D' " END IF END IF set objRecordset = objDBCon.Execute(strSQL) IF NOT objRecordset.EOF THEN IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = " DELETE FROM Load_Control WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Convert(DateTime, Convert(VarChar(10), MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 101)) AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' " set objRecordset = objDBCon.Execute(strSQL) ELSE strSQL = " DELETE FROM Load_Control WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Convert(DateTime, Convert(VarChar(10), MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 101)) " set objRecordset = objDBCon.Execute(strSQL) END IF IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = " Update Tbl_FeedFile Set File_Status = 'C', Task_Status = 'STORED PROCEDURE COMPLETED' WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'NIP' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' " set objRecordset = objDBCon.Execute(strSQL) strSQL = " Update Tbl_FeedFile Set File_Status = 'NIP', Task_Status = NULL WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' " set objRecordset = objDBCon.Execute(strSQL) ELSE strSQL = " Update Tbl_FeedFile Set File_Status = 'C', Task_Status = 'STORED PROCEDURE COMPLETED' WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'NIP' " set objRecordset = objDBCon.Execute(strSQL) strSQL = " Update Tbl_FeedFile Set File_Status = 'NIP', Task_Status = NULL WHERE Source_ID = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND File_Status = 'E' " set objRecordset = objDBCon.Execute(strSQL) END IF END IF GetCTLTimeStamp(DTSGlobalVariables("G_CTL_FILEPATH").Value) ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN 'PROCESS MONTH END AND DAILY FEEDFILES [FREQUENCY) strSQLComplete = " SELECT Count(*) As 'Completed' FROM Tbl_FeedFile TF Inner Join Load_Control LC ON LC.Source_ID = TF.Source_ID AND LC.Table_ID = TF.Table_ID AND LC.File_Name = TF.FeedFile_Name " strSQLComplete = strSQLComplete + " AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) WHERE LC.Status = 'COMPLETE' AND TF.File_Status = 'NIP' AND TF.Frequency = 'M' " set objRecordset = objDBCon.Execute(strSQLComplete) Else 'MSGBOX "PROCESS MONTH END FEEDFILES [ FREQUENCY]" strSQLComplete = " SELECT Count(*) As 'Completed' FROM Tbl_FeedFile TF Inner Join Load_Control LC ON LC.Source_ID = TF.Source_ID AND LC.Table_ID = TF.Table_ID AND LC.File_Name = TF.FeedFile_Name " strSQLComplete = strSQLComplete + " AND Convert(DateTime, Convert(VarChar(10), LC.MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_CTL_TIMESTAMP").Value & "', 101), 101)) WHERE LC.Status = 'COMPLETE' AND TF.File_Status = 'NIP' AND TF.Frequency = 'D' " set objRecordset = objDBCon.Execute(strSQLComplete) End If IF NOT objRecordset.EOF THEN strComplete = objRecordset("Completed") ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN 'PROCESS MONTH END AND DAILY FEEDFILES [FREQUENCY) strTotalSQL = " SELECT Count(*) As 'TotalCount' FROM Tbl_FeedFile WHERE Frequency = 'M' " set objRecordset = objDBCon.Execute(strTotalSQL) Else 'MSGBOX "PROCESS MONTH END FEEDFILES [ FREQUENCY]" strTotalSQL = " SELECT Count(*) As 'TotalCount' FROM Tbl_FeedFile WHERE Frequency = 'D' " set objRecordset = objDBCon.Execute(strTotalSQL) End If IF NOT objRecordset.EOF THEN strTotal = objRecordset("TotalCount") END IF END IF IF strComplete = strTotal THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN IF DTSGlobalVariables("G_SET_LOOPCTL").Value = True THEN GetMoveCTLFile END IF END IF IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQL = "select Source_Id, FeedFile_Name from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'M' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'M' " ELSE IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = "select Source_Id, FeedFile_Name from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " ELSE strSQL = "select Source_Id, FeedFile_Name from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'D' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'D' " END IF END IF set objRecordset = objDBCon.Execute(strSQL) set strRSCount = objDBCon.Execute(strSQLCount) IF NOT strRSCount.EOF THEN strDatDuplicateCount = strRSCount("Count") END IF flag = 0 strDataLoop = 1 While not objRecordset.EOF strDataFileName = objRecordset("FeedFile_Name") strSourceId = objRecordset("Source_id") strDate = Replace( FormatDateTime(Now(), vbGeneralDate),"/","") strDate = Replace( strDate," ","") strDate = Replace( strDate,":","") strDate = Left( strDate ,(Len(strDate)-2) ) IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN If Instr(strDataFileName, ".DAT") > 0 Then strSourceCTLFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & Mid(strDataFileName,1, InstrRev(strDataFileName,".")-1) & ".CTL" strDestinationCTLFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & Mid(strDataFileName,1, InstrRev(strDataFileName,".")-1) & ".CTL" & "_" & strDate END IF ELSE strSourceCTLFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & strSourceId & ".CTL" strDestinationCTLFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & strSourceId & ".CTL" & "_" & strDate END IF strSourceDATFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & strDataFileName strDestinationDATFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & strDataFileName & "_" & strDate strSQL = "select TOP 1 Source_Id, Table_Id, StoredProcedure_Name, FeedFile_Name, Mf_TimeStamp from Tbl_ErrorLog where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' And FeedFile_Name = '"& strDataFileName &"' AND Status = 'STORED PROCEDURE COMPLETED' AND Convert(DateTime, Convert(VarChar(10), MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 101)) " set strRSDuplicate = objDBCon.Execute(strSQL) IF NOT strRSDuplicate.EOF THEN strDuplicateSourceId = strRSDuplicate("Source_id") strDuplicateTableId = strRSDuplicate("Table_id") strDuplicateStoredProcedure = strRSDuplicate("StoredProcedure_Name") strDuplicateFeedFileName = strRSDuplicate("FeedFile_Name") strDuplicateTimeStamp = strRSDuplicate("Mf_TimeStamp") END IF strRSDuplicate.Close IF oFSO.FileExists(strSourceDATFile) THEN oFSO.MoveFile strSourceDATFile, strDestinationDATFile flag = flag + 1 UpdateFeedFile strDuplicateSourceId, strDuplicateTableId, strDuplicateStoredProcedure, strDuplicateFeedFileName, strDuplicateTimeStamp END IF If ( strDatDuplicateCount = flag ) then oFSO.MoveFile strSourceCTLFile, strDestinationCTLFile DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_SOURCEID").Value =null End if strDataLoop = strDataLoop + 1 objRecordset.Movenext WEND GetUpdateFeedFileStatus = True ELSE IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQL = "select Source_Id, Table_Id, FeedFile_Name, File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND Frequency = 'M' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'M' " ELSE IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQL = "select Source_Id, Table_Id, FeedFile_Name, File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " ELSE strSQL = "select Source_Id, Table_Id, FeedFile_Name, File_Status from Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' AND Frequency = 'D' " strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' AND Frequency = 'D' " END IF END IF set objRecordset = objDBCon.Execute(strSQL) set strRSCount = objDBCon.Execute(strSQLCount) strDatCount = strRSCount("Count") flag = 0 strDataLoop = 1 While not objRecordset.EOF strFileStatus = objRecordset("File_Status") strDataFileName = objRecordset("FeedFile_Name") strSourceId = objRecordset("Source_id") strTableId = objRecordset("Table_id") DTSGlobalVariables("G_DB_DAT_FILENAME").Value = strDataFileName DTSGlobalVariables("G_SOURCEID").Value = strSourceId If strFileStatus = "NIP" Then strSQL = "Update Tbl_FeedFile Set File_Status = 'P', Task_Status = 'PROCESS' where source_id = '"& strSourceId &"' and FeedFile_Name = '"& strDataFileName &"'" objDBCon.Execute(strSQL) Elseif Trim(strFileStatus) = "C" Then strDate = Replace( FormatDateTime(Now(), vbGeneralDate),"/","") strDate = Replace( strDate," ","") strDate = Replace( strDate,":","") strDate = Left( strDate ,(Len(strDate)-2) ) IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN If Instr(strDataFileName, ".DAT") > 0 Then strSourceCTLFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & Mid(strDataFileName,1, InstrRev(strDataFileName,".")-1) & ".CTL" strDestinationCTLFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & Mid(strDataFileName,1, InstrRev(strDataFileName,".")-1) & ".CTL" & "_" & strDate END IF ELSE strSourceCTLFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & strSourceId & ".CTL" strDestinationCTLFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & strSourceId & ".CTL" & "_" & strDate END IF strSourceDATFile = DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & strDataFileName strDestinationDATFile = DTSGlobalVariables("G_DUPLICATEPATH").Value & "\" & strDataFileName & "_" & strDate strSQL = "select TOP 1 Source_Id, Table_Id, StoredProcedure_Name, FeedFile_Name, Mf_TimeStamp from Tbl_ErrorLog where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value &"' And FeedFile_Name = '"& strDataFileName &"' AND Status = 'STORED PROCEDURE COMPLETED' AND Convert(DateTime, Convert(VarChar(10), MF_TimeStamp, 101)) = Convert(DateTime, Convert(VarChar(10), Convert(DateTime, '"& DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 101)) " set strRSDuplicate = objDBCon.Execute(strSQL) IF NOT strRSDuplicate.EOF THEN strDuplicateSourceId = strRSDuplicate("Source_id") strDuplicateTableId = strRSDuplicate("Table_id") strDuplicateStoredProcedure = strRSDuplicate("StoredProcedure_Name") strDuplicateFeedFileName = strRSDuplicate("FeedFile_Name") strDuplicateTimeStamp = strRSDuplicate("Mf_TimeStamp") END IF strRSDuplicate.Close IF oFSO.FileExists(strSourceDATFile) THEN oFSO.MoveFile strSourceDATFile, strDestinationDATFile flag = flag + 1 UpdateFeedFile strDuplicateSourceId, strDuplicateTableId, strDuplicateStoredProcedure, strDuplicateFeedFileName, strDuplicateTimeStamp END IF End if strDataLoop = strDataLoop + 1 objRecordset.Movenext WEND IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' And File_Status = 'C' AND Frequency = 'M' " ELSE IF DTSGlobalVariables("G_SOURCEID").Value = "MFEF" THEN strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' And File_Status = 'C' AND Table_Id = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' AND Frequency = 'D' " ELSE strSQLCount = "Select Count(*) As Count From Tbl_FeedFile where Source_Id = '"& DTSGlobalVariables("G_SOURCEID").Value & "' And File_Status = 'C' AND Frequency = 'D' " END IF END IF set strRSCount = objDBCon.Execute(strSQLCount) strCompletedDATCount = strRSCount("Count") IF strDatCount <> 0 AND strCompletedDATCount <> 0 THEN If ( strDatCount = strCompletedDATCount ) then oFSO.MoveFile strSourceCTLFile, strDestinationCTLFile DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_SOURCEID").Value =null DTSGlobalVariables("G_SET_UPDATE_STATUS").Value = 1 GetUpdateFeedFileStatus = True Else GetUpdateFeedFileStatus = False End if END IF ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN 'PROCESS MONTH END AND DAILY FEEDFILES [FREQUENCY) strQuery = " Select Count(*) As Total From Tbl_FeedFile WHERE Frequency = 'M' " set Recordset = objDBCon.Execute(strQuery) IF NOT Recordset.EOF THEN strTotalRecord = Recordset("Total") END IF strQuery = " Select Count(1) As Completed From Tbl_FeedFile Where File_Status = 'C' AND Frequency = 'M' " set Recordset = objDBCon.Execute(strQuery) IF NOT Recordset.EOF THEN IF Recordset("Completed") = strTotalRecord THEN strQuery = "Update Tbl_FeedFile Set File_Status = 'NIP', Task_Status = Null WHERE File_Status = 'C' AND Frequency = 'M' " set Recordset = objDBCon.Execute(strQuery) strQuery = "Update Tbl_FeedFile Set MF_TimeStamp = Null WHERE Frequency = 'M' " set Recordset = objDBCon.Execute(strQuery) DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 DTSGlobalVariables("G_CTL_FILENAME").Value = null END IF END IF ELSE strQuery = " Select Count(*) As Total From Tbl_FeedFile WHERE Frequency = 'D'" set Recordset = objDBCon.Execute(strQuery) IF NOT Recordset.EOF THEN strTotalRecord = Recordset("Total") END IF strQuery = " Select Count(1) As Completed From Tbl_FeedFile Where File_Status = 'C' AND Frequency = 'D' " set Recordset = objDBCon.Execute(strQuery) IF NOT Recordset.EOF THEN IF Recordset("Completed") = strTotalRecord THEN strQuery = "Update Tbl_FeedFile Set File_Status = 'NIP', Task_Status = Null WHERE File_Status = 'C' AND Frequency = 'D' " set Recordset = objDBCon.Execute(strQuery) strQuery = "Update Tbl_FeedFile Set MF_TimeStamp = Null WHERE Frequency = 'D' " set Recordset = objDBCon.Execute(strQuery) DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 DTSGlobalVariables("G_CTL_FILENAME").Value = null END IF END IF END IF END IF END IF End Function Function UpdateFeedFile(getSourceId, getTableId, getStoredProcedure, getFeedFileName, getTimestamp) ' To update Tbl_ErrorLog and Tbl_FeedFile status Dim objDBCon, strCon, strUpdateSQL, strInsertSQL set objDBCon = CreateObject("ADODB.Connection") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon strInsertSQL = " Insert Into Tbl_ErrorLog (Source_ID, Table_ID, StoredProcedure_Name, FeedFile_Name, Mf_TimeStamp, Error_Code, Status, Return_Status, Feed_ProcessTime) " strInsertSQL = strInsertSQL + " Values ( '" & getSourceId &"', '" & getTableId &"', '" & getStoredProcedure &"', '" & getFeedFileName & "', Convert(DateTime, '" & getTimestamp &"', 101), 5, 'DUPLICATE FILE', 1 , Convert(DateTime, '" & DTSGlobalVariables("G_SET_PROCESSTIME").Value & "', 101) ) " objDBCon.Execute(strInsertSQL) objDBCon.Close Set objDBCon = Nothing End Function Function SetJobStartAndEndTime() IF DTSGlobalVariables("G_START_JOBTIME").Value = 0 THEN ' To get and set job start time and total job time Dim objDBCon, objRS, strJobTime set objDBCon = CreateObject("ADODB.Connection") set objRS = CreateObject("ADODB.Recordset") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon strQuery = " Select JobTime From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed' " Set objRS = objDBCon.Execute( strQuery) IF NOT objRS.EOF THEN strJobTime = objRS("JobTime") strQuery = " Update Tbl_ConfigDTS Set Job_StartTime = GetDate(), Job_EndTime = DateAdd(hh, "& strJobTime &", GetDate()) Where DTSName = 'DTS_BNR_CPS_LoadFeed' " ' strQuery = " Update Tbl_ConfigDTS Set Job_StartTime = GetDate(), Job_EndTime = DateAdd(n, 5, GetDate()) Where DTSName = 'DTS_BNR_CPS_LoadFeed' " objDBCon.Execute (strQuery) strQuery = " Select Job_StartTime From Tbl_ConfigDTS Where DTSName = 'DTS_BNR_CPS_LoadFeed' " Set objRS = objDBCon.Execute( strQuery) IF NOT objRS.EOF THEN DTSGlobalVariables("G_SET_PROCESSTIME").Value = objRS("Job_StartTime") END IF DTSGlobalVariables("G_START_JOBTIME").Value = 1 END IF objRS.Close objDBCon.Close Set objDBCon = Nothing END IF End Function Function CanProceedDTSJob() ' Function to proceed the DTS package Dim objDBCon,strCon,strSQLQuery IF WaitForCTLFeedFile(DTSGlobalVariables("G_CTL_FOLDERPATH").Value) = True Then 'MsgBox " FeedFile Yet to arrive. You can proceed the DTS Job To RUN..." CanProceedDTSJob = True ELSE 'MsgBox "All the feed files have arrived stop the Job. Job is running for more than two hours" CanProceedDTSJob = False End IF End Function Function FolderContainsCTLFile(strFolderPath) ' To check CTL file in the folder Dim oFileObject,oFolder SET oFileObject = CreateObject("Scripting.FileSystemObject") strFile=null SET oFolder = oFileObject.GetFolder(strFolderPath) SET oFolderFiles = oFolder.Files FolderContainsCTLFile = False FOR EACH oFile IN oFolderFiles strFile = Mid ( oFile, InstrRev(oFile,"\")+1,Len(oFile) - InstrRev(oFile,"\")) strFullFileName = strFile if InStr(strFile, ".CTL") > 0 then FolderContainsCTLFile = True Exit For End If NEXT set oFileObject = Nothing End Function Function GetCTLTimeStamp(strFile) Dim strTimeStamp IF InStr(strFile, strSearch) THEN Dim strNextLine Const ForReading = 1 Set objFSO = CreateObject("Scripting.FileSystemObject") IF objFSO.FileExists(strFile) THEN Set objTextFile = objFSO.OpenTextFile (strFile, 1) Do Until objTextFile.AtEndOfStream strNextLine = objTextFile.Readline arrCTLLINE = Split(strNextLine , vbTab) IF IsDate( arrCTLLINE(2)) THEN strTimeStamp = arrCTLLINE(2) END IF Loop DTSGlobalVariables("G_CTL_TIMESTAMP").Value = strTimeStamp DTSGlobalVariables("G_DAT_TIMESTAMP").Value = strTimeStamp END IF Set objFSO = nothing Set objTextFile = nothing END IF End Function Function FindNextMonthFirstDate(CurrentDate) 'Finding Next Month First Date Dim NextMonthsFirstDay,NextMonth,NextMonthYear NextMonthsFirstDay = "01" NextMonth = Month(CurrentDate) NextMonthYear = Int(Year(CurrentDate)) If NextMonth < 12 then NextMonth = NextMonth + 1 Else NextMonth = 1 NextMonthYear = NextMonthYear + 1 End If FindNextMonthFirstDate = DateSerial( NextMonthYear , NextMonth , NextMonthsFirstDay ) ' FindNextMonthFirstDate = CDate(NextMonth & "/" & NextMonthsFirstDay & "/" & NextMonthYear) End Function 'Function GetEndTime(objDBCon) Function GetEndTime Dim objDBConHr, strConHr Set objDBConHr= Createobject("ADODB.Connection") strConHr = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBConHr.Open strConHr strSQLHourQuery = " Select DatePart(hh, GetDate()) As JobHour " set objRecordset = objDBConHr.Execute(strSQLHourQuery) IF NOT objRecordset.EOF Then strJobHour = objRecordset("JobHour") END IF GetEndTime = strJobHour objDBConHr.Close Set objDBConHr = Nothing End Function 'Function GetEndmin(objDBCon) Function GetEndmin Dim objDBConMn, strConMn Set objDBConMn= Createobject("ADODB.Connection") strConMn = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBConMn.Open strConMn strSQLMinQuery = " Select DatePart(n, GetDate()) As JobMin" set objRecordset = objDBConMn.Execute(strSQLMinQuery) IF NOT objRecordset.EOF Then strJobMin = objRecordset("JobMin") END IF GetEndmin = strJobMin objDBConMn.Close Set objDBConMn = Nothing End Function Function AddFileNotArrivedStatus(objDBCon) CurrentDate = Date ' If DateDiff("d",CurrentDate,FindLastWorkingDayOfTheMonth(CurrentDate)) = 0 then IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "M" THEN strSQLNIP = " Select Source_Id, Table_Id, FeedFile_Name From Tbl_FeedFile Where File_Status = 'NIP' And Frequency = 'M' " set Recordset = objDBCon.Execute(strSQLNIP) DO WHILE NOT Recordset.EOF strInsertSQL = " Insert Into Tbl_ErrorLog (Source_ID, Table_ID, FeedFile_Name, Mf_TimeStamp, Error_Code, Status, Return_Status, Feed_ProcessTime) " strInsertSQL = strInsertSQL + " Values ( '" & Recordset("Source_Id") &"', '" & Recordset("Table_Id") &"', '" & Recordset("FeedFile_Name") & "', Convert(DateTime, '" & DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 90, 'FILE NOT ARRIVED TO THE FEEDFILES FOLDER', 1, Convert(DateTime, '" & DTSGlobalVariables("G_SET_PROCESSTIME").Value & "', 101) ) " objDBCon.Execute(strInsertSQL) Recordset.MoveNext LOOP DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 DTSGlobalVariables("G_CTL_FILENAME").Value = null ELSE strSQLNIP = " Select Source_Id, Table_Id, FeedFile_Name, Mf_TimeStamp From Tbl_FeedFile Where File_Status = 'NIP' And Frequency = 'D' " set Recordset = objDBCon.Execute(strSQLNIP) DO WHILE NOT Recordset.EOF strInsertSQL = " Insert Into Tbl_ErrorLog (Source_ID, Table_ID, FeedFile_Name, Mf_TimeStamp, Error_Code, Status, Return_Status, Feed_ProcessTime) " strInsertSQL = strInsertSQL + " Values ( '" & Recordset("Source_Id") &"', '" & Recordset("Table_Id") &"', '" & Recordset("FeedFile_Name") & "', Convert(DateTime, '" & DTSGlobalVariables("G_DAT_TIMESTAMP").Value & "', 101), 90, 'FILE NOT ARRIVED TO THE FEEDFILES FOLDER', 1, Convert(DateTime, '" & DTSGlobalVariables("G_SET_PROCESSTIME").Value & "', 101) ) " objDBCon.Execute(strInsertSQL) Recordset.MoveNext LOOP DTSGlobalVariables("G_START_JOBTIME").Value = 0 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 DTSGlobalVariables("G_CTL_FILENAME").Value = null END IF End Function Function GetLineCountFromTheFile(strPath_FileName,Ctlfilename) Dim objDBCon,strCon,strSQLQuery,FileLineCount,arrCtlLine,Strdatfile Dim FSO, file, TextStream, Line Const ForReading = 1 Const TristateUseDefault = -2 FileLineCount = 0 set FSO = createObject("Scripting.FileSystemObject") set file = FSO.GetFile(strPath_FileName) Set TextStream = file.OpenAsTextStream(ForReading, TristateUseDefault) ' Read the file line by line Do While Not TextStream.AtEndOfStream Line = TextStream.readline arrCtlLine = Split(Line , vbTab) IF arrCtlLine(0) <> "" AND arrCtlLine(1) <> "" AND arrCtlLine(4) <> "" THEN DTSGlobalVariables("G_SOURCEID").Value = arrCtlLine(0) DTSGlobalVariables("G_DAT_TABLENAME").Value = arrCtlLine(1) DTSGlobalVariables("G_SET_FEEDFILENAME").Value = arrCtlLine(4) END IF Loop End Function Function FindLastWorkingDayOfTheMonth(CurrentDate) Dim LastDayofTheMonth LastDayofTheMonth = FindLastDateOfTheMonth(CurrentDate) Select Case WeekDay(LastDayofTheMonth) Case 1 FindLastWorkingDayofTheMonth = DateAdd("d",LastDayofTheMonth,-2) Case 7 FindLastWorkingDayofTheMonth = DateAdd("d",LastDayofTheMonth,-1) Case Else FindLastWorkingDayofTheMonth = LastDayofTheMonth End Select End Function Function FindLastDateOfTheMonth(CurrentDate) FindLastDateOfTheMonth = DateAdd("d",FindNextMonthFirstDate(CurrentDate),-1) End Function Function ResetGlobalVariables() Dim objDBCon, objRS, strJobTime set objDBCon = CreateObject("ADODB.Connection") set objRS = CreateObject("ADODB.Recordset") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon strQuery = " Select Top 1 'ErrorOccured' As 'ErrorOccured' From Tbl_ConfigDTS Where DTSName = 'SetGlobalValue' " Set objRS = objDBCon.Execute( strQuery) IF NOT objRS.EOF THEN DTSGlobalVariables("G_CHKLOOPEXIT").Value = 0 DTSGlobalVariables("G_START_JOBTIME").Value = 0 strQuery = " Delete From Tbl_ConfigDTS Where DTSName = 'SetGlobalValue' " objDBCon.Execute( strQuery) MoveDailyCTLToMainFolder() END IF objRS.Close objDBCon.Close Set objDBCon = Nothing End Function Function SetGlobalValue() SET oFileObject = CreateObject("Scripting.FileSystemObject") IF oFileObject.FileExists( DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\DEF8.CTL") Then DTSGlobalVariables("G_CTL_FILEPATH").Value= DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\DEF8.CTL" DTSGlobalVariables("G_CTL_FILENAME").Value = "DEF8.CTL" DTSGlobalVariables("G_SOURCEID").Value = "DEF8" DTSGlobalVariables("G_DAT_TABLENAME").Value = "F202" Else DTSGlobalVariables("G_CTL_FILEPATH").Value= DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\DEF7.CTL" DTSGlobalVariables("G_CTL_FILENAME").Value = "DEF7.CTL" DTSGlobalVariables("G_SOURCEID").Value = "DEF7" DTSGlobalVariables("G_DAT_TABLENAME").Value = "F20B" End if End Function Function MoveCTLFeedFile(strSourceFolderPath_FileName,strDestinationFolderPath_FileName) ' Move .CTL file to respective folder Dim strExt,strDesPath,strSource,strDesFile, strDate Dim fso Set fso = CreateObject("Scripting.FileSystemObject") strDestinationFolderPath_FileName = strDestinationFolderPath_FileName SET objFile = CreateObject("Scripting.FileSystemObject") 'IF fso.fileexists(strSourceFolderPath_FileName) Then IF strSourceFolderPath_FileName <> "" AND strDestinationFolderPath_FileName <> "" THEN objFile.MoveFile strSourceFolderPath_FileName, strDestinationFolderPath_FileName END IF 'END IF set objFile=Nothing End Function Function GetMoveCTLFile() ' Get the first CTL file Dim oFileObject,oFolder,iFileCount ,strFile,strFullFileName,iFeedFormat Dim objDBCon, objRS set objDBCon = CreateObject("ADODB.Connection") set objRS = CreateObject("ADODB.Recordset") strCon = DTSGlobalVariables("G_XFERCTL_CONNECT").Value objDBCon.Open strCon SET oFileObject = CreateObject("Scripting.FileSystemObject") DTSGlobalVariables("G_CTL_FILENAME").Value = null DTSGlobalVariables("G_DB_DAT_FILENAME").Value =null strFile=null SET oFolder = oFileObject.GetFolder(DTSGlobalVariables("G_CTL_FOLDERPATH").Value) SET oFolderFiles = oFolder.Files iFileCount =0 'Get files from Feedfiles folder DO WHILE DTSGlobalVariables("G_SET_LOOPCTL").Value = True FOR EACH oFile IN oFolderFiles strFile = Mid ( oFile, InstrRev(oFile,"\")+1,Len(oFile) - InstrRev(oFile,"\")) strFullFileName = Mid(strFile,1, InstrRev(strFile,".")-1) IF instr(strFile, ".CTL") > 0 THEN Call GetLineCountFromTheFile(oFile,oFile.Name) IF DTSGlobalVariables("G_SOURCEID").Value <> "" AND DTSGlobalVariables("G_DAT_TABLENAME").Value <> "" AND DTSGlobalVariables("G_SET_FEEDFILENAME").Value <> "" THEN strQuery = " Select Top 1 Frequency From Tbl_FeedFile Where Source_ID = '" & DTSGlobalVariables("G_SOURCEID").Value & "' And Table_ID = '" & DTSGlobalVariables("G_DAT_TABLENAME").Value & "' And FeedFile_Name = '" & DTSGlobalVariables("G_SET_FEEDFILENAME").Value & "' AND Frequency <> 'D' " Set objRS = objDBCon.Execute( strQuery) DTSGlobalVariables("G_CTL_FILENAME").Value = oFile.Name IF NOT objRS.EOF THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN ' There is only Daily process so Move Month CTL Feed File to TempMonthly folder Call MoveCTLFeedFile(DTSGlobalVariables("G_CTL_FOLDERPATH").Value & "\" & DTSGlobalVariables("G_CTL_FILENAME").Value, DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value & "\" & DTSGlobalVariables("G_CTL_FILENAME").Value) END IF DTSGlobalVariables("G_SET_LOOPCTL").Value = True DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 ELSE DTSGlobalVariables("G_SET_LOOPCTL").Value = False EXIT DO END IF END IF EXIT FOR EXIT DO END IF NEXT IF WaitForCTLFeedFile(DTSGlobalVariables("G_CTL_FOLDERPATH").Value) = False THEN IF DTSGlobalVariables("G_SET_PROCESSTYPE").Value = "D" THEN DTSGlobalVariables("G_SET_STATUS_MOVEFILE").Value = 1 DTSGlobalVariables("G_CHKLOOPEXIT").Value = 1 EXIT DO END IF END IF LOOP End function Function MoveTempToMain(strSourceFolderPath_FileName,strDestinationFolderPath_FileName) ' Move .CTL file to Main folder Dim strExt,strDesPath,strSource,strDesFile, strDate Dim fso Set fso = CreateObject("Scripting.FileSystemObject") SET objFile = CreateObject("Scripting.FileSystemObject") objFile.MoveFile strSourceFolderPath_FileName, strDestinationFolderPath_FileName set objFile=Nothing End Function Function MoveCTLFeedFile1(strSourceFolderPath_FileName,strDestinationFolderPath_FileName) ' Move .CTL file to respective folder Dim strExt,strDesPath,strSource,strDesFile, strDate Dim fso Set fso = CreateObject("Scripting.FileSystemObject") strDestinationFolderPath_FileName = strDestinationFolderPath_FileName SET objFile = CreateObject("Scripting.FileSystemObject") 'IF fso.fileexists(strSourceFolderPath_FileName) Then IF strSourceFolderPath_FileName <> "" AND strDestinationFolderPath_FileName <> "" THEN objFile.MoveFile strSourceFolderPath_FileName, strDestinationFolderPath_FileName END IF 'END IF set objFile=Nothing End Function Function IsFolderEmpty(strFolderPath) Dim objFSO, folderStatus Set objFSO = CreateObject("Scripting.FileSystemObject") Set folderStatus = objFSO.GetFolder(strFolderPath) If folderStatus.Size=0 Then IsFolderEmpty = True Else IsFolderEmpty = False End If Set objFSO = Nothing End Function Function MoveDailyCTLToMainFolder() ' change this function to move monthly files when package abends. IF IsFolderEmpty(DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value) = False THEN Dim oFileObject, oFolder, oFolderFiles, objFile SET oFileObject = CreateObject("Scripting.FileSystemObject") SET oFolder = oFileObject.GetFolder( DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value ) SET oFolderFiles = oFolder.Files oFileObject.MoveFile DTSGlobalVariables("G_TEMPMONTHLY_PATH").Value & "\" & "*.CTL", DTSGlobalVariables("G_CTL_FOLDERPATH").Value DTSGlobalVariables("G_CTL_FILENAME").Value = null Set oFileObject = Nothing END IF End Function Function RemoveArchiveFailedDuplicateFiles(folderpath) Dim oFileObject, oFolder, oFolderFiles Dim objFSO, days_diff SET oFileObject = CreateObject("Scripting.FileSystemObject") SET oFolder = oFileObject.GetFolder(folderpath) SET oFolderFiles = oFolder.Files FOR EACH oFile IN oFolderFiles days_diff = datediff("d",oFile.DateCreated,date) if days_diff >DTSGlobalVariables("G_DAY_TOREMOVE_FILES").Value then oFileObject.deletefile(oFile) end if Next End Function Thanks & Regards, Karthikeyan

Hi, i;m not sure where to put my function main(). please advice. Not able to provide the script here... pls provide ur mail id, i can send the script. Thanks & Regards, Karthikeyan

Error: 0xC0048006 at Script to chk size of ctl file, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x0204A7EC. Task failed: Script to chk size of ctl file Perhaps just an idea, but I think you should try to Modification in your DTS (SQL Server 2000): rename/change the name to have no spaces and no non-alphabet chars, and Rerun the upgrade wizard. If the upgrade fails again then the best approach is probably to simple re-code the task in .netArthur My Blog

You may try to add similar lines as the previous thread such as some comments, option explicit/on error resume clauses, dummy variable declarations etc. before the line of your "function Main()" Thanks. Liu An - MSFT