Hello,

I am new to dts and have the following problem. I have to import a
file of the following format:

head 1 <TAB> val1
head 2 <TAB> val2
head 3 <TAB> val3
.... etc...
1. <TAB> val <TAB> val <TAB> val <tab> val
2. <TAB> val <TAB> val <TAB> val <tab> val
3. <TAB> val <TAB> val <TAB> val <tab> val
4. <TAB> val <TAB> val <TAB> val <tab> val
....etc...

The file is <TAB> delimitered. The first part (a header) is to be
inserted in the master table and the second part is to be inserted
into the details table. The header is structured like this: [name of
column] <TAB> [value in column]. The detais part is in normal columnar
layout. Could anybody help me acomplish this using dts (I use sql
server 2000)?


Thanks,
Bialy

I would use VB to parse the file first. The text file driver is quirky. I
would split the file into 2. One would contain the header data and the
other would be the child table data.

If your rowcount is small you may want to avoid even this overhead and
simply use VB to do the opening/parsing of the text file and then inserting
into the database

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Bialy" <bialy (AT) klub (DOT) chip.pl> wrote

Thanks Allan,

Do you mean that I have to open the file using VB script, read the
header and then use the data pump to load the rest of the file?

I have got one more question. In the situation that I have here there
will be many (approx. 100) such files in a directory on a remote ftp
server. Is it possible to create a dts script that would cycle through
all the files and import data to the db? How do I accomplish this
using dts?

One more thing (sorry but I am really new to dts): how do I execute
sql statements from inside VB script?

Thank you very much for your reply,
Bialy


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Looping through a directory is easy using the FileSystemObject. You can do
that in VBScript yes. Because I think you would need to split the current
file into two more (1 header and 1 data) and then use DTS to import them I
would probably cut out the middleman and not use DTS per se. I would loop
through the directory and pass the filename off to a parsing routine. This
routine would then write the data to SQL Server. I would write the data
using ADO and some stored procs which accepted parameters.

I would FTP all the files down into a common directory first and then
archive afterwards.

To use DTS datapumpss you are probably looking at

FTP DOWN
Parse
Write out header and data files from Source File
set properties in package to these filenames
Pump Header
Pump Footer
Archive
Loop



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Bialy" <bialy (AT) klub (DOT) chip.pl> wrote

Thanks Allan,

I have got one more question. The names of the files on the remote ftp
server will change (ie. the file name convention is something like
this: id,date_of_creation,time_of_creation.txt). How do I set up dts
file transfer protocol task to download all the files (asI don't know
their names at designtime) or download the whole dir containing them?
I understand that the File System Object lets me cycle through all the
files but on local machine only - not on ftp server. I have looked at
the file transfer protocol task and it only lets me choose specific
files not a mask like *.txt...

Your help will be very apperciated,
Thank you,
Bialy


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote