Obviously the first thing to check is that you have set the correct password for the user in the DTS Connection. Reset the password to make sure. The DTS Connection dialog will validate the details when closing.

From time to time you may experience unexpected login failures even after re-setting the username and password in a DTS connection. The "Persist Security Info" OLE-DB property specifies whether to save the password within the connection or not, and sometimes this is defaults to false (0) without warning.

To check or fix this problem, open the DTS Connection dialog, and click Advanced. Persist Security Info is usually the top property in the data-grid, and should be set to true (1 or –1).