This example shows how you can use two DTS packages to control the import of all files in a given directory.
For this, it is assumed that all files have the same format, and that all files are imported into the same table.
Feel free to expand on this to fit your own solution.
(This example uses the Microsoft FileSystemObject)
Package One (DTSDirDemo-Loop) has just an Active Script Task, with the following code -
'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Option Explicit Function Main() 'Const DTSSQLStgFlag_UseTrustedConnection = 256 Dim oPKG Dim i Dim sFolder sFolder = "C:\DemoImport\" Dim fso, f, f1, fc, s Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.GetFolder(sFolder) Set fc = f.Files For Each f1 in fc Set oPKG = CreateObject("DTS.Package") oPKG.LoadFromSQLServer ".", , , 256, , , , "LoopDemo-Import" Set cn = oPKG.Connections("Connection 1") cn.DataSource = sFolder & f1.name oPKG.Execute oPKG.Uninitialize() Set oPKG = Nothing Next Main = DTSTaskExecResult_Success End Function |
Package Two (DTSDirDemo-Import) has a Text File Source connection, (Connection 1), a server connection, and a transform task between the two. A T-SQL task is used to create the table if it does not exist. This was created with the Import Wizard, nothing fancy here!
For some more information about changing filenames and other connection properties see How to dynamically change...Connections.
The two sample packages can be downloaded below. To use -
A table called [ImportTest] will be created in master DB.
There are ten records in "ImportTest.txt", so the record count of [ImportTest] will be 10 X Number of "ImportTest.txt"
copies in "C:\DemoImport\".
Files contained in DTSDirDemoPkg.zip -
Download DTSDirDemoPkg sample.