I reckon Data Transformation Services in SQL Server 2000 are a good thing, and have used them to import and process text files into my databases. With a bit of mucking around you can do almost anything with DTS, like checking for file existence and setting the target database, among other things. You can even execute a DTS package from a stored procedure, which is perfect for calling from a custom user interface, setting global variables and doing extra processing.
A rough example:
DECLARE @rc INT, --return value from "exec" calls
@pkg INT --pointer to package ('object token')
--create an instance of the DTS package
EXEC @rc = sp_OACreate 'DTS.Package', @pkg output
--test return value (this should happen after every sp_OA* call)
IF @rc <> 0 BEGIN
--error handling
END
--get the DTS package by name
EXEC @rc = sp_OAMethod @pkg, 'LoadFromSQLServer', NULL, @ServerName = @@SERVERNAME,
@Flags = 256, --NT authentication
@PackageName = "<PACKAGE NAME>"
--set properties of the package
EXEC @rc = sp_OASetProperty @pkg, 'GlobalVariables ("<GLOBAL VARIABLE NAME>").Value', <GLOBAL VARIABLE VALUE>
--also set the "fail on error" property to True
EXEC @rc = sp_OASetProperty @pkg, 'FailOnError', -1
--execute the package
EXEC @rc = sp_OAMethod @pkg, 'Execute', NULL
--kill our local instance of the package
EXEC sp_OADestroy @pkg
The major (and I mean major) drawback to this approach is that the sp_OA* extended stored procedure calls can only be executed by members of the sysadmin fixed role. Not something I want to give to my end users!
I only discovered this after developing a system for a whole year and then deploying on a live server. There does not appear to be a workaround - I've read that granting EXECUTE permission on the extended stored procedures in question might be enough, but this means that a user can create any object and run it...a Very Bad Thing.
Today I started changing my import plumbing to use a VB.NET class to execute the DTS package, ala http://support.microsoft.com/?kbid=321525 and http://www.sqldts.com/default.aspx?265 and it looks like one of the drawbacks is that I'm going to have to work out which DLL's need to be deployed on the user's PC (http://www.winnetmag.com/SQLServer/Article/ArticleID/7444/7444.html gives some help).
I've learned two lessons from this (and I know there's more to come): know your deployment environments (both back-end and front-end), and beware of sp_OACreate.
UPDATE: Two days of work and my two DTS packages are up and running using VB.NET. One key difference is that using VB.NET to call the DTS COM DLL means that the package runs on the local machine, not the server. The initial hurdle of required files to distribute when the end-user does not have Enterprise Manager was solved when I added the required DLL's to my installer, set the COMSelfReg property for particular DLL files and re-installed on the client machine.
I also took the time to make some improvements to my packages - previously I had done some processing using a stored proc, then imported the file using DTS, then done some more processing using a stored proc; now I have rolled that all into the one DTS job, which seems much neater. One further improvement of using VB.NET with a package event handler is that progress events can be displayed to the user (like the Enterprise Manager interface).
I feel like this new approach has some downsides. Using DTS to call a stored procedure and passing both input & output parameters is very unintuitive (see http://www.sqldts.com/?234 for a workaround). And, setting the value of “string” Global Variables from VB.NET is terrible, the workaround is to remove the Global Variable and re-add it (see http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=Z73MR1URCHA.2644%40cpmsftngxa06&rnum=1&prev=/groups%3Fq%3DConclusions%2BIn%2Border%2Bto%2Bchange%2Bthe%2Bvalue%2Bof%2Ba%2BDTS%2BGlobal%2BVariable%2Bof%2Btype%2BString%2Bfrom%2BVB.Net%2Bcode%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DZ73MR1URCHA.2644%2540cpmsftngxa06%26rnum%3D1 for complete information).
Apart from this I'm happy that it only took two days to keep my import routine working, and remove reliance on sp_OACreate.