Sometimes the actual error lies somewhere and the error message directs us to look at something else. We end up wasting time in this process as the error message diverted us. Recently, we came across one such kind of scenario while calling a SSIS package in one of the Job steps on SQL Server 2008. The details are as follows.
Error: Login failed for user 'Domain\alias'. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot open database "MyDatabase" requested by the login. The login failed.". End Error Error: 2009-02-26 03:08:23.93 Code: 0xC020801C Source: DFT-MyTask OLE DB Source  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MyServer.MyDatabase" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2009-02-26 03:08:23.93 Code: 0xC0047017 Source: DFT-MyTask SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2009-02-26 03:08:23.93 Source: DFT-MyTask Validating: 50% complete End Progress Error: 2009-02-26 03:08:23.93 Code: 0xC004700C Source: DFT-MyTask SSIS.Pipeline Description: One or more component failed validation. End Error Error
Job Logic: We are using a script task to dynamically set the connection manager. We are pulling data from a source server using a select query and putting it into a table on a destination server. We are surprised to see login failed error in the first part of the message. Because, the account "Domain\alias" is a sysadmin on the source as well as destination server and the job is also running under the same account.
Analysis: Since the account is sysadmin, the error message is misleading. Also, the second message was of no use as it didn't detail why it couln't acquire connection inspite of having the right permissions. Interestingly, the crux lies in the last part of the message where it says validation failed.
Fix: After breaking our heads why the validation has failed, we couldn't find any reason but noticed that the DFT-MyTask has "DelayValidation" property set to false in the package. So, we changed the DelayValidation property of DFT-MyTask to true and re-ran the job and it succeeded.
Root Cause: As per our understanding, if DelayValidation is not set to true, SSIS engine uses the design time values of a task until it actually runs a task. Since we are dynamically setting the connection manager, instead of using the dynamically set value (Which it uses anayway while running the task), it tried to validate the task with the design time values set for the connection manager. Unfortunately, the service account with which the job is running doesn't have access to the server and database set during the design time for the connection manager. So, the validation of the task failed causing the job to fail.