Hello,

The contract administration department in my organization is evaluating
a software package that uses SQL Server 2000. They would like to
import data from the mainframe to this database. Essentially, they
would press a function key which would create a flat file containing
vendor name, contract number, and sponsor. This file would be FTP'd to
a file server and then be imported into SQL Server.

I've been fooling around a little with the Data Transformation Service
and it looks like this can be done. I have a couple of concerns that
I'm hoping someone here could answer. First, while insertion of new
rows seems to be easily done, I don't see anything about updating
existing data. Is it possible to do this relatively easily? Second,
once the data has been imported, does DTS have any command to delete
the text file? I don't want
DTS to continue importing the same rows every time, as I figure on
having this job run every 15-30 minutes to bring in any new data that
might be out there.

Appreciate any help on this.

John Yager

Create a package that imports the text file into a staging table.
Use TSQL to do the INSERTs or UPDATEs

Deleting a file.

Can you not have the FTP process overwrite?
What about moving the file for archiving and audit using FSO?
(FileSystemObject)

Allan

"johnyager (AT) yahoo (DOT) com" <johnyager (AT) yahoo (DOT) com> wrote

if the FTP process won't overwrite, or you are concerned with importing the
same data twice because a new file wasn't present when your package ran, use
an activeX script in DTS to delete the file once imported successfully, or
you can create a batch file on the filesystem that deletes the file, and you
can call that batch file from within DTS in an Execute process task, or
within ActiveX script using a filesystem object.

--
Simon Worth


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Simon Worth wrote:
Thanks, Simon and Allen. Your information helped out a lot.

John