Programatic Import (Space delimited) import into SQL Server 2000

I am trying to find a way to do a programatic import of a space delimited
file into a SQL Server 2000 database.
The file is actually an IIS web log file. I can run through the file and
strip out the header information, I can even create a seperate file for each
change in header information is needed.
The problem becomes importing that file into the database. The log files are
on average 6MB each. The import will need to run daily on just a single log
file (after previous log files have been imported)
I do not want to use BULK INSERT because I have had a lot of inconsistant
problems with that functionality in the past.
Does anyone have any ideas?
John WycoffI would start with the DTS data import wizard. You can specify a text
file as input, and space as the column delimiter, and the package it
generates can be saved and modified later. It does assume that all
the rows are in the same format.
Roy Harvey
Beacon Falls, CT
On Thu, 20 Jul 2006 07:15:01 -0700, John Wycoff <John
Wycoff@.discussions.microsoft.com> wrote:
>I am trying to find a way to do a programatic import of a space delimited
>file into a SQL Server 2000 database.
>The file is actually an IIS web log file. I can run through the file and
>strip out the header information, I can even create a seperate file for each
>change in header information is needed.
>The problem becomes importing that file into the database. The log files are
>on average 6MB each. The import will need to run daily on just a single log
>file (after previous log files have been imported)
>I do not want to use BULK INSERT because I have had a lot of inconsistant
>problems with that functionality in the past.
>Does anyone have any ideas?
>John Wycoff|||Use DTS. If the file is 'space' delimited, or is fixed position, DTS can
handle the import. You can create a DTS 'Package' (the set of instructions),
and then schedule that Package to run when appropriate.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Wycoff" <John Wycoff@.discussions.microsoft.com> wrote in message
news:170BB6CA-D3E7-4BEA-A336-390BF6A3DF9E@.microsoft.com...
> I am trying to find a way to do a programatic import of a space delimited
> file into a SQL Server 2000 database.
> The file is actually an IIS web log file. I can run through the file and
> strip out the header information, I can even create a seperate file for
> each
> change in header information is needed.
> The problem becomes importing that file into the database. The log files
> are
> on average 6MB each. The import will need to run daily on just a single
> log
> file (after previous log files have been imported)
> I do not want to use BULK INSERT because I have had a lot of inconsistant
> problems with that functionality in the past.
> Does anyone have any ideas?
> John Wycoff|||The problem is that I do not know what the filenames will be. And the formats
will be different. The closest I can get is to run through the log files
generating a seperate log file for every different format. But I cannot
programattically change the DTS package everytime, nor can I tell the DTS
package to import every file in a specific directory.|||On Tue, 25 Jul 2006 06:08:02 -0700, John Wycoff
<JohnWycoff@.discussions.microsoft.com> wrote:
>The problem is that I do not know what the filenames will be. And the formats
>will be different. The closest I can get is to run through the log files
>generating a seperate log file for every different format. But I cannot
>programattically change the DTS package everytime, nor can I tell the DTS
>package to import every file in a specific directory.
I am still pretty unclear about what the files look like. How many
different tables is the data being loaded into? Do the headers have
any data you need? Is it one header per file? Or a header, followed
by several items, then another header and more items?
It IS possible to get DTS to import every file in a folder. There is
VBA coding involved.
Roy Harvey
Beacon Falls, CT