Hello,

I am embarking on a project using DTS and could use some advice on how
to go about it. I need to import multiple text files to sql tables
from a directory.
The text file names will vary based on a combination of an association
number and a club number. A text file name will look like CADJaaaccc
where aaa = assoc number and ccc = club number and the "prefix",
"CADJ" remains constant.

My routine should detect the existence of the text file perform the
import 1 to 1 to an SQL table then archive the original in a different
directory. The text files are created on an ir-regular schedule about
every two weeks. These subsequent SQL tables will then be queried to
display data on the web via asp.

I've gone through many tutorials that show how to import multiple text
files to one sql table. I am getting a grip on creating and scheduling
a single package but could use some advice on expanding the concept to
a multi-table, variable table name scenerio. Any help is appreciatted!

Thanks in advanced,

Mick Force
Web Developer
www.handinet.com

See
http://www.nigelrivett.net/ImportTextFiles.html

That's for bulk insert but if you really want to usr dts you can do a similar thing setting a global variable using dtsrun then using a dynamic properties task to set it as the source file.

"Mickfo" wrote:

Nigel Rivett <NigelRivett (AT) discussions (DOT) microsoft.com> wrote

Thanks Nigel,

Sorry for the late response (Out of the office last week).

Could you take a look at my "logic" and let me know if I am on-track
or not?
Forgive me if I am totally off-track as I am primarily a web developer
who has been thrown into this task!

I created a .bat file (which I'll schedule to run later)as follows:

------------------------------------------------------------------
dir D:\FTPSites\Landing\CADJ\*.TXT /b >
D:\FTPSites\Landing\CADJ\DirList.csv
for /F "tokens=1,2,3" %%i in (D:\FTPSites\Landing\CADJ\DirList.csv) do
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSrun.exe" -F
"D:\FTPSites\Landing\CADJ\CADJ_DTS_PACKAGE1.dt s" -Uxxxxxxx -Pxxxxxxx
-A"ServerName"="XXXXXX" -A"FileName"="D:\FTPSites\Landing\CADJ\%%i"
-A"DatabaseName"="XXXXXX"
ren D:\FTPSites\Landing\CADJ\*.txt *.txtold
ren D:\FTPSites\Landing\CADJ\*.csv *.csvold
------------------------------------------------------------------

The batch file passes Global Variables to a DTS package that first
deletes the contents of the staging table (statstage) then imports the
..txt file to the staging table which works well.

The next task at hand (which I am stumped as how to do it yet) is as
follows:

Extend the DTS package to "transform" each record type in "statstage"
to it's own table. I am using a staging table because each .txt file
contains 4 different record types (Shown in the example below). I
believe that I can sort on Col001 (A,B,C or D) in "statstage" to
accomplish this (via a DDQ?).

CADJaaaccc.txt contains the following:
-------------------------------------------
"A",0,"06/25/04","11:57:57",2,101,"RGN",191,"BLOSSOM TRAILS GOLF CLUB

"B",1,85.9,86.1,84.5,67.2,85.9,87.1,85.4,999.9,82. 2,82.6,999.9,81.8,"BACK
TEE",89.3,"FORWARD TEE",92.5,"MIDDLE TEE"

"C",1,0.0,68.0,1,0.8,99.2

"D",1,5016373,90.7,"90.7",13,90.7,90.7,999.9,999.9 ,90.7,90.7,999.9,999.9,90.3,999.9,999.9,999.9,999. 9,90.7
---------------------------------------

The subsequent tables will have a name derived from the source text
file name like CADJaaaccc_A (that I hopefully can create using the
filename Global variable).

From there I can query these tables and display the data on the web.

That's were I'm at, any help is profoundly appreciated as I am getting
closer to my deadline!