Import multiple files using DTS

[welshone]

hello,
~I have created a simple import rountine using DTS, which imports one file.
however, how can I modify this package to import an entire folder of files . ?

thanks for any info, help.
J.

 

[DLSQL]

Assumptions SQL 2000 on Windows 2000 Server or greater.
I am also going to assume that the files contain similar data, and are going to be inserted into the same table.

What you will need to do is use an ActiveX Task Script to use the FileSystemObject to get a list of files from the directory, then store the first filename in a global variable, then set a Dynamic properties task to start. the Dynamic Properties task then sets the file source of a connection object to point to the new file (based on the global variable). Then the Data Pump task fires. After the data pump task then the active x task fires and is able to loop through the whole thing.

Function Main()
'I am going from memory so some of the File System Object stuff may be off a little.
Dim oPkg
set oPkg = DTSGlobalVariables.Parent
Dim oFSO , oDir, oF
Set oFSO= CreateObject("Scripting.FileSystemObject&quot
Set oDir = oFSO.GetDirectory("c:\import\&quot
'Loop of somesort
Set oF = oDir.Files(x)

If oF.Name = DTSGlobalVariables("LastFile&quot.Value Then
Set oF = oDir.Files(x +1)
DTSGlobalVariables("LastFile&quot.Value = oF.Name
Exit loop
End If
if x = oDir.Files.Count Then
oPkg.Steps("Whatever you finish up tasks are&quot.ExecutionStatus _
= DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
Exit Function

End If
End loop

oPkg.Steps("Dynamic Properties Task 1&quot.ExecutionStatus _
= DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success

End Function


DL
MCDBA, MCSD, MCT, etc.