SQL Server text file continuous import problem

[Chris]

Background: I am using a MS Access 2000 front end with SQL Server 8.0
back end.

I have the requirement to import all text files (regardless of
filename) from a given folder on the network into a table within SQL
Server. This import needs to run continuously, as more text files
will be saved in the folder by a separate system and they need to be
updated into the SQL Server table. I have a DTS which can import all
text files from the folder into the SQL Server table.

The problem is in triggering the DTS continuously. (The Schedule
Package only runs at a maximum of once a minute, which is not quick
enough.)

Can anyone answer any, or all, of the following:

1. Is there a way of executing the DTS every time a text file appears
in the given folder?

2. Can ANY DTS be run continuously through a loop (either via a
stored procedure or a 2nd DTS) without affecting any other processes
in the SQL Server? I.e., how can you trigger a DTS to run again as
soon as it has completed, regardless of success or failure?

3. Can you reference / execute a DTS within a stored procedure, and
if so, how is this done?
In addition, I need to have a button on the MS Access 2000 GUI that
runs the DTS in case of failure, as a kind of manual update.

4. Is it possible to run a DTS from the Access front end, and, if
yes, how is it done?

[Allan Mitchell]

Wow

Yes you can execute a package on creation of a text file by building a
FileSystemWatcher application in .Net and having that fire the package.
That way you could pass the name of the file as a variable to the package
and only import that file.

Alternatively you do not use DTS at all and simply have the
FileSystemWatcher application in .Net do the Import of the text file as
well.
Kepping looping on a DTS package? You could I suppose finish the package
with an ExecutePackage task for itself but this could/would get nasty.<g>

You can execute a package through TSQL yes

Execute a package from T-SQL
(http://www.sqldts.com/default.aspx?210)
Yep you can run DTS from Access. You need the DTS Dlls

Redistributing DTS with your program
(http://www.sqldts.com/default.aspx?225)

Execute a package from Visual Basic (VB)
(http://www.sqldts.com/default.aspx?208)
--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Chris" <ch***********@yahoo.co.uk> wrote in message
news:63**************************@posting.google.c om...

Background: I am using a MS Access 2000 front end with SQL Server 8.0
back end.

I have the requirement to import all text files (regardless of
filename) from a given folder on the network into a table within SQL
Server. This import needs to run continuously, as more text files
will be saved in the folder by a separate system and they need to be
updated into the SQL Server table. I have a DTS which can import all
text files from the folder into the SQL Server table.

The problem is in triggering the DTS continuously. (The Schedule
Package only runs at a maximum of once a minute, which is not quick
enough.)

Can anyone answer any, or all, of the following:

1. Is there a way of executing the DTS every time a text file appears
in the given folder?

2. Can ANY DTS be run continuously through a loop (either via a
stored procedure or a 2nd DTS) without affecting any other processes
in the SQL Server? I.e., how can you trigger a DTS to run again as
soon as it has completed, regardless of success or failure?

3. Can you reference / execute a DTS within a stored procedure, and
if so, how is this done?
In addition, I need to have a button on the MS Access 2000 GUI that
runs the DTS in case of failure, as a kind of manual update.

4. Is it possible to run a DTS from the Access front end, and, if
yes, how is it done?