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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 16, 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 
		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.



  • MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    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


















    Thanks for your registration, follow us on our social networks to keep up-to-date