Hello!

I'm very new to SSIS and I'm trying to transfer all our existing mssql 2000
databases to 2005. I'm trying this with the Transfer Database Task, but it is
giving me trouble.

I could successfully connect to the source and destination database and
select the database I wish to transfer but within the window that appears
when wish to select the DestinationDatabaseFiles, I cannot do anything and in
the SourceDatabaseFiles an error appears (An exception occurred while
executing a Transact-SQL statement or batch). I also cannot execute the
batch, it abords.

What am I doing wrong? Is there a tutorial around on how to do this? As far
as I'm concerned this should be a quite simple thing.

Thanks very much for your help.
Brian

Hello Brian2049,

Instead of using this task I would be inclined to do a BACKUP and RESTORE.
it will probably be cleaner.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Dear Allan

Thanks for your response! I tried that previously but that wasn't successful
either. I faced the problem that when I restored the database on the 2005
server it didn't create a security-login for the database user. Within the
database though, the user was created.

So I wanted to create a security-login manually but then the error message
appeared that this user already existed. And deleting the user from the
database didn't work either, since he's the owner. Along with that I coulnd't
remove the permissions of the user.

Am I getting this completely wrong?
Thanks for any help.
Brian.

Hello Brian2049,

So when you restore the Db change the owner to somebody valid.
Now remove/ or remap the previous orphaned logins - (have a look at sp_change
users_login)






Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Allan

I managed to get it to run by using the BACKUP and RESTORE procedure. I did
the following:

1. Restore database
2. Removing the schema of the previous user
3. Deleted the user
4. Created a new user with the same credentials
5. Assigned appropriate permissions to the database
6. Connect worked!

So the mistake I made is that I didn't was aware of that the schema of the
orphaned user needed to be deleted in order to assign a new user.

What I still don't get is why this isn't possible in an orderly fashion
using the "Transfer Database Task" of the SSIS.

Once again, thanks for your help.
Brian.