Transfer Database Task in SSIS 2008

Question

I am trying to use SSIS to transfer a database from a SQL 2000 instance to SQL 2008 using a Transfer Database Task.

I've tried everything that I can think of to get this to work including using accounts with full access to both databases in the Connection Managers. However, each time I try to run it, I get the following:

    Information: 0x400292A8 at Transfer Customizations, Transfer Database Task: Overwriting the database "Customizations" on the destination server "Server2".
Error: 0xC0024104 at Transfer Customizations: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_grantdbaccess @loginame = N'Reports', ..." failed with the following error: "'Reports' is not a valid login or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}). The Execute method must succeed, and indicate the result using an "out" parameter.
Task failed: Transfer Customizations

"Reports" is a real login with read only access. However, the 1st Server which uses the login (the source) isn't even in the connection manager for the source so I am confused as to why it comes up in the error.

Any ideas?

Answer 1

Not too sure about the error but f you are just looking to move / copy a database then it may be easier just to detach and reattach the MDF file.

There is a step by step guid here