I heard about SSIS, a new replacement of the old DTS. I believe it has the same functionality which I used to perform. Now, like any other product from Microsoft, you have to mess with 10 different choices for a single package.
I would like to get your opinion of which edition of SQL Server 2005 I should buy to ensure that I can replicate data between my host server and the local data store just the way DTS worked?
Sorry dude, but SSIS is not as easy and solid as DTS... if possible I would stay with a copy of SQL 2000 to use DTS
You can install the backward compatibility components for SQL Server 2005 which allows you to run your old DTS packages in SQL Server 2005 (and 2008). Here's the link :
You can use DTS in SQL Server 2005. My departmental server was upgraded to SQL Server 2005 from SQL Server 2000. My DTS packages were moved as well. You will need the Microsoft SQL Server 2000 DTS Designer Components in order to edit them.
http://msdn.microsoft.com/en-us/sqlserver/aa336314.aspx
The drawback to this is that it will be very hard to create new packages. You can do it, but you'll need to first export an existing package, change it, then import it with a new name.
SSIS has a pretty high learning curve. I've been using it for a couple years now, and it is a step forward in some things, but backwards in others.
I believe you probably want SQL Server 2005 - Workgroup edition.
The SSIS is not available in the Express version, and as long as you are not wanting to replicate the data out to various subscribers then Workgroup is probably all you need. Here is a chart comparing the various features of 2005.
Before you decide to purchase a version, I would download the trial of the particular version you are interested in and verify it gives you the functionality needed.
You'll want to keep this in mind after you've upgraded, if you're doing web development:
One difference between DTS and SSIS packages is, if you want to execute them through code, although you have more control over the package with SSIS, the machine the code is executing from is required to have SQL server installed on it. Which means if you're writing this as an ASP.Net application, for example, whatever machine(s) it ends up running on will also need to have sql server installed on them (which is unlikely in most environments)