Create Procedure TextImport 
	@PkgName varchar(50), @Server varchar(25), 
  @FileConnName varchar(50), 
  @ClearTableName varchar(30), 
  @FilePath varchar(8000)
Declare @Pkg int, @dataSource varchar(50), @conn int, 
@hr int
Set nocount on
Exec @hr = sp_OACreate 'DTS.Package', @Pkg out
-- Error handling goes here after each automation call
-- see BOL for sp_OAGetErrorInfo 
-- Load the Package definition 
-- Flags is for Trusted Connection
Exec @hr = sp_OAMethod @pkg, 'LoadFromSQLServer', 
NULL, @Server, @PackageName = @PkgName, @Flags = 256
-- Get a handle to the TextFile connection
Declare @propName varchar(50)
Set @propName = 'connections(' + @fileConnName + ')'
Exec @hr = sp_OAGetProperty @Pkg, @propName, @conn out
- Optionally clear the destination table
Declare @stmt nvarchar(100)
If DataLength(@clearTableName) > 1
	BEGIN
	Select @stmt = N'delete ' + @clearTableName
	Exec @hr = sp_executesql @stmt
	END
-- @FilePath can have multiple full path file specs 
-- separated by commas (no trailing comma)
Declare @temp varchar(8000), @ipos int, @total int
Set @total = Datalength(@filePath)
Set @ipos = CharIndex(',',@filepath)
While @ipos > 0
	BEGIN
	Select @temp = left(@filePath, @ipos - 1)
	Select @filepath = substring(@filepath,.@ipos + 1, 
@total - @ipos + 1)
	Set @ipos = CharIndex(',',.@filepath)
-- Set the FilePath and Execute the Package
	Exec @hr = sp_OASetProperty @conn, 'datasource', @temp
	Exec @hr = sp_Oamethod @pkg, 'Execute'
	END
-- Execute the first file (when only one) or the 
-- last file when more than one
Exec @hr = sp_OASetProperty @conn, 'datasource', @filepath
Exec @hr = sp_Oamethod @pkg, 'Execute'
-- Cleanup
Exec @hr = sp_OADestroy @conn
Exec @hr = sp_OADestroy @pkg

/* Call the Stored Procedure as follows to clear the destination table and import three text files: */
Exec 'ImportText', 'MyServerName', 'TextFile', 'MyTableName', 
	'\\Machine\Share\File1,\\Machine\Share\File2,\\Machine\Share\File3'

