Transfer Logins Task fails with the error message: "Set Database failed. "

Description:

While migrating the logins with the "Transfer Logins Task" from SQL Server Integration Services (SSIS) you might receive the following error message:

Error: Execution failed with the following error: "Set Database failed. ".
Error: Execution failed with the following error: "Value cannot be null. Parameter name: database".

Cause:

This happens because one of the logins has a database set per default which no longer exists. If you open for example in SQL Server Management Studio (SSMS) the login, you will see that nothing is selected under the default database.

Resolution:

To find exactly which logins are affected and the database the user tries to access, run the following command:

SELECT p.name AS login,p.default_database_name,'ALTER LOGIN ['+p.name+'] WITH DEFAULT_DATABASE=[master];' 'Fix T-SQL'
FROM sys.server_principals p
LEFT JOIN sys.databases d ON d.name=p.default_database_name
WHERE d.name IS NULL
AND p.default_database_name IS NOT NULL

The last column contains the T-SQL script to change the default database to master for those particular logins.