Situation: I'm in charge of decommissioning a SW product with the Back-End in a SQL Server database. We approximately 90 clients that we will have to pull multiple tables down into a combo of CSV and XML files on disk for them to reference once the SW is gone. Each client has its own separate SQL DB.
I have many of the SQL Scripts and SP's written or mostly written to dump the data to tables in the SQL BE. I can automate the linking to the tables from an Access DB to read and export to the appropriate files.
The databases are all SQL 2005 Standard edition and it is an Access 2003 front-end.
The Issue: My T-SQL scripts and custom SP's to extract the data to the tables runs into several thousand lines of code already. I'm going to have "novices" in SQL that would have to run the scripts for each SQL DB and then fire up the Access to run the exports. I'd like to automate the process of running the SQL Scripts from the Access DB. The steps as I envision them:
They fire up Access -- it creates a DSN and auto links to the master.sys.database table.
Then a form pops up saying "this is your choice of databases/clients.
They pick the DB and then Access runs the T-SQL scripts against that database.
Then Access links to the export tables and will start dumping data to disk.
Step 3 is where I'm getting stuck trying to think around the corner. I want to make it easy for the techs running this to "click" and go. I'm trying to avoid having them do too many steps.
I don't want to bother with SSIS because of the limited amount of time to build the packages and the "one-time" use of this package.
Any thoughts on how to do this?