Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 17, 2000

Import all files in a directory (Multi Pkg)

By Darren Green

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 
		Set oPKG = Nothing
	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.

  • MS SQL Archives

    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM