I work for an environmental agency. I need to import data from a text file
every 30 minutes. This text file contains weather info which is collected
every minutes by an instrument in the Gulf. So every minutes a line of data
is added to the text file. Then every 30 minutes, I need to read the text
file, and transfer 30 lines of data to a SQL table. Can DTS do this? I have
successfully used DTS to create a blank table in SQL and write data from text
file to that SQL table. But I wonder DTS will be able to handle the every 30
minutes job but selecting only new records in the text file. Do I have to
write a program in VBscript to do it? Any suggestions will be appreciated.
Sincerely,
Pelican

DTS certainly can import from a text file into a SQL Server table. The
scheduling aspect is implemented by calling the package from a Job, which
you configure to execute every 30 minutes. The part I'm confused about is
"selecting only new records". Will this text file grow in length
indefinately? Although you could program some VBScript to perhaps filter in
only the latest text records using an ID or timestamp value, this would be
troublesome, and the performace would degrade over time as the file gets
larger with unneeded text records. Once the data is imported, there should
be no need to retain old text records on file? If not, you can add a task to
the DTS package which renames or deletes the text file after importing it,
which will solve this problem.


"pelican" <pelican (AT) discussions (DOT) microsoft.com> wrote

Thank you very much, JohnnyAppleseed. The text file only contains one day's
data. It gets refreshed every day. So at the beginning of hte day, there is
no data in it. one minute after mid night, the instrument writes a line of
data. Then the next minute, it writes another. at 12:30, we need to write
all the 30 lines of data to SQL. At 1:00 am, we need to write data froom
12:30 to 1:00 to SQL. Each line of data has a minute like "1", "2",
"3",...."101", "102" to identify itself. That is a unique key. So every 30
minutes when we are ready to write data to SQL, we want to start from
earliest new one in the text file. I do not know if DTS can handle this kind
of logic. What do you think? Any suggestions will be appreciated.

Sincerely,

Pelican

"JohnnyAppleseed" wrote:

In your DTS package,

- execute sql task: truncate table1
- transfer data in text file to table1
- transfer data in table1 to table2 using a query that filters only the
records you need.


"pelican" <Pelican (AT) discussions (DOT) microsoft.com> wrote

JohnnyAppleseed,
Thank you. I have a question, why do I need to truncate table 1? Is it
because table1 is used as a intermediate table between the text and
destination file, and truncate means to delete all data in it?

"JohnnyAppleseed" wrote:

Yes, table1 is an intermediate table that will contain a full import of the
text file. Table2 is a subset of table1 containing only the records you
need.

"pelican" <pelican (AT) discussions (DOT) microsoft.com> wrote