[quoted text, click to view] Nigel Rivett <NigelRivett@discussions.microsoft.com> wrote in message news:<8B759D7A-24C8-4601-94DC-60BC827F5C10@microsoft.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:
>
> > 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 > >
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.dts" -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
