I am getting the following error when my DTS package is scheduled:
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
I am not sure how to get my DTS packages to execute. They run fine when I manually execute them
I am running SQL Server 2005. DTS Packages were created using the Import/Export Wizard.
Most likely a permission issue, what user does the job run under and does that user have all the needed permissions?
When a package is scheduled, it normally runs under the context and persmissions of the SQL Server Agent.
Does the package make a connection to a drive letter or other mechanism which is not available to the unattended sqlserver agent account?
I think the problem is with the login/password combination in any of the connections in the connection manager of the DTS, or some problem with the access to the server where the db is located.
If your server name is over 31 characters long (including hostname), then it will not fire.
Have you checked the excellent KB article regarding security issues with Agent:
http://support.microsoft.com/default.aspx/kb/918760
It lists many possible causes, but most importantly - at the end KB describes how to troubleshoot this by enabling logging and getting detailed error information.
I changed authentication from SQL Server to Windows Auth and it did the trick. Not sure the technical reason why.