Hi,

I have 100 tab-delimited text files I have to import every day into
their respective tables on SQL Server 2000. The data in each of the
files is not homogenious, i.e. some files have 3 columns, some have 50
and weigh 200 Mb, etc. I download all files from an ftp site.

The big picture is this: all txt files have to be imported into SQL
Server tables, I don't care how.

Before I start creating 100 separate text file connections inside a
DTS, is there any way to make a loop? Perhaps an ActiveX script that
browses through a list of files in a dir/table, dynamically changes the
destination file, etc? If so, how do I dynamically set the
transformations to accomodate each file's different data structure?

Thanks in advance.

You will have to tweak this a bit and populate the schema.ini for your
own purposes and compose logic for figuring which schema (template) to
use for which files.

ENJOY

'************************************************* *********************
' VB_LoadTextFiles
'************************************************* ***********************
Option Explicit
Function Main()
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim sInFileSpecs, sInFN, sOutFN, sSchemaFN, sSQL
Dim sDTSHome, fulldir, folder, files, file1, nFileCount

sDTSHome = "D:\DTS"

sSchemaFN = sDTSHome & "\Schema.ini"
sInFileSpecs = "in*.txt"
sOutFN = sDTSHome & "\TEMP.TXT"

' schema.ini file must exist
If Not fso.FileExists(sSchemaFN) Then
Err.Raise -1, "VB_LoadTextFiles", "Schema.ini file missing, aborted
processing.. (" & sSchemaFN & ")"
Main = DTSTaskExecResult_Failure
Exit Function
End If

' search path for source files is batch file directory
fulldir =
fso.GetParentFolderName(DTSGlobalVariables("FTPGet Command").Value)
Set folder = fso.GetFolder(fulldir)
Set files = folder.files
nFileCount = 0
' for all files
For Each file1 In files
' is file a targeted file?
If UCase(Left(file1.Name, 20)) = "APBSCANCH1-BSC(USD)-" And
InStr(1, UCase(file1.Name), ".TXT") <> 0 Then
sInFN = fulldir + "\" + file1.Name
' copy source file into schema expected file name
fso.CopyFile sInFN, sOutFN, True
nFileCount = nFileCount + 1

' remove tmp_template1 if it exists
sSQL = "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'dbo.tmp_template1') AND OBJECTPROPERTY(id, N'IsUserTable')
= 1) DROP TABLE dbo.tmp_template1"
TSQL_Execute_Fetch sSQL

sSQL = "SELECT * INTO dbo.tmp_template1 SELECT " &
DTSGlobalVariables("BatchId").Value & " AS batch_id, * " _
& " FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver
(*.txt; *.csv)};DefaultDir=" & sDTSHome & ";','SELECT * FROM
TEMP.TXT')"
TSQL_Execute_Fetch sSQL
fso.DeleteFile sOutFN
End If
Next

Set fso = Nothing

Main = DTSTaskExecResult_Success
End Function

'************************************************* *********************
Function TSQL_Execute_Fetch(TSQL_Command)
Dim cn, rst
Dim oPkg
Set oPkg = DTSGLobalVariables.Parent
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial
Catalog=YOURDATABASENAME;Application=TSQL_Execute_ Fetch;Data
Source=YOURSERVERNAME"
Set rst = CreateObject("ADODB.Recordset")

rst.Open TSQL_Command, cn
TSQL_Execute_Fetch = ""
On Error Resume Next
TSQL_Execute_Fetch = rst.Fields(0)
rst.Close
On Error Goto 0
Set rst = Nothing

'clean up
cn.Close
Set cn = Nothing
End Function

sample schema.ini (SUGGEST YOU RETAIN TEXT VS DATA TYPES SO YOU CAN
CATCH EXCEPTIONS VS HAVING THE DRIVER CRASH YOUR SCRIPT
[TEMP.TXT]
Format=FixedLength
CharacterSet=ANSI
ColNameHeader=False
Col1=VendorId Text Width 8
Col2=VendorInvoiceNumber Text Width 20
Col3=InvoiceDate Text Width 10
Col4=GLAccountCode Text Width 20
Col5=LineNumber Text Width 4
Col6=InvoiceAmount Text Width 16
Col7=APChargeAmount Text Width 16
Col8=AccountNumber Text Width 30




ThreeHun wrote:

Thank you for such a helpful and well-formed response.

I've modded your code to fit my needs as far as files, paths, etc.
However, I'm still having trouble understanding where the actual
importing into the SQL Server 2000 tables takes place.

Thanks again.

Slice wrote:

code says:
"SELECT * INTO dbo.tmp_template1 SELECT ..."

means
SELECT [ALL] INTO <somewhere> SELECT <something>

Creates a table on the fly. You can conversly use "INSERT INTO
<somewhere> SELECT <something>" if the targeted tables already exist.

Thanks again.

Why are you hardcoding the file name into the "is file a targeted
file?" loop?

If UCase(Left(file1.Name, 20)) = "APBSCANCH1-BSC(USD) ...

Why do you need that check?



Slice wrote:

This check assumes that the directory may have other files that you do
not want to import. Unfortunately, the VBA Files collection does not
have an easy wildcard method of selection.

ThreeHun wrote: