Hi DBA Gurus,

I have no. of text files in one directory. I want to read them all and
import in a DB. Once succeeded I want to move the files to different
directory. I have written an ActiveX script to read the files and process
them. It is processing only one file and DTS finishes. It is not looping
through. Any help is much appreciated.

The ActiveX script is as follows.

Option Explicit

Function Main()
Dim objFSO, objFolder, objFiles, objFile, strPath, destPath,
objMail,today
Dim adminEmail, reportEmail
Dim dt
dt = Now
If Month(dt) < 10 Then
today = Year(dt) & "0" & Month(dt) & Day(dt)
Else
today = Year(dt) & Month(dt) & Day(dt)
End If

adminEmail = DTSGlobalVariables("adminEmail").value
reportEmail = DTSGlobalVariables("reportEmail").value

Set objFSO = CreateObject("Scripting.FileSystemObject")
strPath = DTSGlobalVariables("filePath").Value
destPath = DTSGlobalVariables("destPath").Value
Set objFolder = objFSO.GetFolder(strPath)
Set objFiles = objFolder.Files
Dim numbe_of_files
numbe_of_files = 0
For Each objFile in objFiles
If Left(objFile.Name,8) = today Then
numbe_of_files = numbe_of_files + 1
End If
Next
If numbe_of_files = 0 Then
Set objMail = CreateObject("CDO.Message")
objMail.To = adminEmail
objMail.From = reportEmail
objMail.Subject = "Response Logs - No files to process"
objMail.TextBody = "This email has been generated
automatically from the Response Log Import DTS package. There is no response
log waiting to be processed. Please investigate"


objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configur
ation/sendusing") = 2

objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configur
ation/smtpserver") = "smtp"

objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configur
ation/smtpserverport") = 25
objMail.Configuration.Fields.Update
objMail.Send
Set objMail = Nothing
Main = DTSTaskExecResult_Failure

Else

For Each objFile in objFiles

If Left(objFile.Name,8) = today Then
Dim objConn
Set objConn =
DTSGlobalVariables.Parent.Connections("Connection 12")
objConn.DataSource = objFile.Path
Set objConn = Nothing
DTSGlobalVariables("fileName").Value =
objFile.Name
Main = DTSTaskExecResult_Success
End if
Next
End If
Set objFiles = Nothing
Set objFolder = Nothing
Set objFSO = Nothing
End Function


MS Sql Server LazyDBA home page