How about creating a .bat file to combine all your txt files into one,
then dts it into the db?

-----Original Message-----
From: Sethuraman Seeni
[mailto:mssqldba-ezmlmshield-x48066593.[Email address protected]
Sent: Wednesday, June 29, 2005 8:50 AM
To: LazyDBA Discussion
Subject: DTS


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/conf
igur
ation/sendusing") = 2

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

objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/conf
igur
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



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page