Inserting single record files into SQL 2K5 based on file's existen

microsoft.public.sqlserver.programming

23 Jun 2006 2:39 PM

David Bowles

We are in the process of migrating from an in-house 'ERP' system to Dynamics
AX and until we have our AX Returns Subsystem up and running, we want to
create a process that if files containing one record each appear in a folder,
we want SQL 2005 to insert them into a given table.

I have already setup a similar process with an SQL Server Agent job
utilizing xp_fileexists as one step, a bulk insert statement as second step,
and an xp_cmdshell deleting the source file as a third step.

My concerns with this process are as follows:
1.) You can only setup an Agent Job to run at minimum every 1 minute every
day. I'd like to have a process that is triggered by a file's existence
rather than looking every minute to see if a file exists. My reasoning lies
within concerns of performance and processor time being eaten by a process
that polls endlessly every 60 seconds.

2.) As I alluded to above, I am concerned with performance issues and
whether or not this is the best method (using Agent Jobs) because we could
potentially have 30-50 separate Agent Jobs running polling directories for
different types of records to insert into different tables.

3.) As far as I can tell, I can't setup the Agent Job to pull in filenames
with wildcards. I'd like to be able to pull in multiple files based upon a
similar filename like RECORD_FILE_yyyymmdd_hhnnss.csv, where I'd specify the
input file as 'RECORD_FILE_*'.

Overall my first question is if using SQL Server Agent Jobs given then
scenario above is the best method of achieving what I am wanting and if so,
how I can supply the xp_fileexists, BULK INSERT, and xp_cmdshell a provision
for being able to act upon files of different names where the difference
equals the date/time stamp in the filename, and I can make sure that when I
delete a file it deletes the one it just inserted into the table rather than
one it hasn't inserted yet.

Thanks in Advance,

David