How do you prevent a scheduled package from failing when the password of the user who originally scheduled the package changes.
Here is the chain of events which lead up to the problem.
Using Enterprise Manager, I connect to the sql server using the user id of meuser01.
I schedule a DTS package to run every Monday.
The scheduled job runs fine for several weeks.
The password for meuser01 expires and is changed.
The next Monday, the schedule job dies with the following error
Executed as user: cwgsqlagent. DTSRun: Loading... Error: -2147217843 (80040E4D);
Provider Error: 18456 (4818) Error string: Login failed for user 'meuser01'. Error source:
Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The
(In the above error you can see that I have already tried to get around the problem by changing the owner of the scheduled job to cwgsqlagent.)
I appreciate any help you can give me.