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 –
Download DTSDirDemoPkg sample.
