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 -

  • Extract archive
  • Load both packages and save as Server packages
  • In [DTSDirDemo-Import], check the server name of Connection 2, currently set to local. If you change it reject the option to reset the transformations.
  • Create directory "C:\DemoImport\" and place in it "ImportTest.txt". Remember that DTS paths are local to the machine they are executed from, not the server.
  • Make multiple copies of "ImportTest.txt", all in "C:\DemoImport\"
  • Execute [DTSDirDemo-Loop] package
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 -

  • DTSDirDemo-Loop.dts
  • DTSDirDemo-Import.dts
  • ImportTest.txt

    Download DTSDirDemoPkg sample.