G'day Chris, Thats terrific for migrating something that's already written in SQL2000. But you can't write DTS packages for SQL2005 - you have to use the "new improved, far more flexible" SSIS for anything new, so far as I know. (if anyone knows different, I'd be delighted to hear so!)
And doing that means writing a package that takes about 5-10 minutes for each table, instead of just selecting them all in a few seconds like the old DTS took. Where once, to set up a DTS package to copy a database took about 5 minutes, now it can take an hour or more, and even then the SSIS package wont duplicate the table structure, only move data. You still have to set up a SQL package to duplicate the table structure on the target database. Then once you have written the program for SSIS every time you want to run it, you have to start the Business Intelligence Development Studio, wait for 20 minutes or more while it does what ever it is doing to start up (I assume validating every last table in every database it's been in touch with in history), load the SSIS solution you want, then wait while it validates all over again. (And if the reason you are opening it, is because you have moved your 48 databases to a new server you have to wait while every last table validation times out and gives an error - it took me 3 hours to start the studio after I moved my database server - just to start it up!!!). There is no way I've found to run the packages automatically as i used to with DTS. Once i had DTS packages running every night to copy my remote databases to my dev server, so i always had current data to develop with, and as a kind of backup of the nightly backups my hosting company was doing. (I reasoned that Murphy's law would apply - when I needed to go back to backup, that would inevitably be the day something would have gone wrong with the nightly backups). I sure can't sit there for hours a day doing database copies. I'm not even sure i can justify doing it weekly except that i'm terrified of Murphy's law and know that the week I tell myself it's ok to miss doing it, will be the week something goes wrong. So while SSIS is wonderfully flexible and programmable, its a LONG way from being as useful as DTS was. At least in my experience. If anyone can point me at where i've missed something I'll be most grateful, because every time i touch that wretched thing i get grumpy and everyone in my family runs for cover. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 10/4/06, Peterson, Chris <[EMAIL PROTECTED]> wrote: > Rick, > > When I was ready to roll out SQL2005, I just setup a VM-Ware server and > did a full restore of my current SQL 2000 into it. Then I tested an > in-place upgrade to make sure everything migrated (it did) > > I just did my live 2005 roll out this past weekend, and it went without > a hitch. =) Everything, including DTS packages, imported without any > probs at all. > > Also, you can leave your SQL tables in 2000 compatibility mode if you > still have some T-SQL statements that have not quite been tested yet (it > defaults to 2000 compatibility mode) > > Good luck, help this helps. > > Chris ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255309 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
