I need to do the following for a client.
1. Download by FTP a zipped file that contains 10 text files
2. Unzip the file to get to the text files
3. Import data from the text files into separate tables in SQL Server.
Text files are pipe-delimited.
This is a hosted sql server...therefore I don't have full admin rights or access to other folders on the sql box.
Can this be done using a dts? I've never used DTS, but it seems like this is possible just messing around with the DTS
Can the unzip part be scripted using a activeX/com component?
Could I use my local server to download the file, then use my local enterprise manager and create the dts to import the data into their remote db?
Yes this can be done. You'll probably have to download the FTP task from www.sqldts.com. I'm not sure if the zip process can be done via activeX but it can definately be done using xp_cmdshell. As far as your local server, it can be done that way but sounds risky to me for a production process. Why not deploy the DTS package to the host SQL Server and ftp the files to a network location then import the data into the tables?