Our company is in the process of re-writing our web app and re-designing the supporting database. We will be needing to convert the data in 400+ tables to the new database and I'm unsure as to which method to use.
This is a an application that is sold to clients so this will need to be a process that can be shipped out and ran by the client to convert their data. A lot of the client will be using only SQL 2000 and others may be running 2005 but using the 2000 compatibility mode.
I would like to be able to do this all in SSIS and would work great for our internal database and 2005 clients, but I'm unaware of a method of using an SSIS package against a 2000 database. I know I can execute other SSIS packages against 2000 from the Business Intelligence Studio, but there are clients that won't have that installed. Is there a reliable way to deloy an SSIS package against a 2000 database this way?
I could use DTS and then require the legacy support be put in for 2005, but honetly I'd so much rather use SSIS for a project this large.
I could also write a whole bunch of scripts and stored procedures. This will get the job done but is just a nightmare to keep track of that much transformation.
Anybody perhaps run into this problem or have any good suggestions?