Jeremy writes "I have users sending me text files everyday. The text file needs to be imported when received. Each file contains the same data, but not always in the same order. Not all of the data is required so some files will have more columns than others. The first line of the file is the column headings. I have written two DTS packages. The first one is an ActiveX Script that steps through the directory looking at the files and setting the properties of the second package and then executing it. The second package just imports the file. The first package is getting all the files but the second package is not always importing all of them. I think the problem lies in the transformation of the data. I am missing something. I am running SQL7.0 on NT4SP4. I have not upgraded to SQLSP3 yet, but I am willing to. I am sure there is a way to do this in SQL as I have written a very simple procedure that gets this done in Access. I know if anybody can get this figured out you guys can. Here is the ActiveX code in both Packages.
Package 1 called StepThroughFiles
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oFS
Dim colFile
Dim oFolder
Dim oFile
Set oPkg = DTSGlobalVariables.Parent
sUploadDirectory = DTSGlobalVariables("gsUploadDirectory").Value
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oRemotePkg = CreateObject("DTS.Package")
oRemotePkg.LoadFromSQLServer "JESS", "sa","",,,,,"Import_File"
Set oFolder = oFS.GetFolder(sUploadDirectory)
Set colFile = oFolder.Files
For Each oFile in colFile
' MsgBox "Filename = " & oFile
If right(oFile.name,4) = DTSGlobalVariables("gsExtention").value Then
Var1 = oFile.name
' MsgBox Left(Var1,InStr(Var1,".")-1)
oRemotePkg.GlobalVariables("custid").value = Left(Var1,InStr(Var1,".")-1)
Set oCon1 = oRemotePkg.Connections("TextIN")
' MsgBox "Old DataSource = " & oCon1.DataSource
oCon1.DataSource = oFile
' MsgBox "New DataSource = " & oCon1.DataSource
oRemotePkg.Execute
' oFile.Delete
end if
Next
Main = DTSTaskExecResult_Success
End Function
Package 2 called Import_File
'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************
Function Main()
DTSDestination("custid") = DTSGlobalVariables("custid").value
For each oColumn in DTSSource
' MsgBox oColumn.name
DTSDestination(oColumn.name) = oColumn
Next
Main = DTSTransformStat_OK
End Function
"
DTS and multiple text files 
Posted - 2001-01-08 : 12:46:24