Hi,

I want to organize data import from text files to SQL Server.
The files are created by outer system on some events in this system,
and then are sent via ftp to a server, where SQL Server is installed.

I want to start DTS automatically in the moment the file is
ftp-uploaded. The synchronus way is very important. I don't want to use
any polling method., e.g. to look every minute in the ftp folder whether
the new file has just been created/uploaded...

I thought, I could use MS IIS FTP service and ODBC logging.
I created 'ftplog' table in SQL, and built a trigger on insert which
starts DTS.
It works fine, but...

A trigger must call xp_cmdshell to start DTS. I don't like to give this
permission to the 'ftp odbc logging user'.

Seems, I've chosen the wrong way.
In the moment, I have no idea for any better solution.

Any hint...?

Regards,
Filip

Have a windows service use the FileSystemWatcher in .Net and watch the
directory. It's what I do.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Filip" <efko (AT) gazeta (DOT) pl> wrote

Hi!

"Allan Mitchell" wrote:

FileSystemWatcher raises 'Created' event immediately after the file has been
created - and what if someone transfers a very large file? Is there a bether
way than:

// --- wait for file transfer completed
while (! TryToOpenInExclusiveMode())
{
Sleep(someTime);
}
// --- file transfer completed

Best regards,
Piotrek