Getting details from SSIS logging

I thought I should step back a moment before continuing to the second most common reason for the AcquireConnection method call failed error and discuss how to get information from SSIS logging.

Logging is not configured by default so it has to be set up in BIDS. Even with logging configured, however, not all details will be logged to the log provider used. For instance, in my previous post where I discussed the AcquireConnection method call failed error, I talked about one cause being related to 32 bit providers in 64 bit environments. In the video embedded in that particular post, I showed the following error message from the BIDS Execution Results window:

[Connection manager "c:\ssis\ExcelFile.xlsx"] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.

However, for the person investigating a package failure by analyzing SSIS log files, for instance, the above error detail won’t be found. Instead, all they will see is the error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "c:\ssis\ExcelFile.xlsx" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Note, however, the highlighted error code, 0xC0209303.  If investigating purely from what has been provided in the log file, this error code will need to be manually interpreted by searching for it in the following article:

SSIS Error and Message Reference

Here is what the article provides in reference to the 0xC0209303 error code:

If the Diagnostic logging event has been selected when the logging was configured for the Package, the log should include clues before the AcquireConnection method call failed error to help in determining provider details.  In this case, the following Diagnostic events were logged:

 ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource(NULL, CLSCTX_INPROC_SERVER, ConnectionString: Data Source=c:\ssis\ExcelFile.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Extended Properties="Excel 12.0;HDR=YES";,...)'.

ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The external request has completed.

So, with a little effort, you can get more details from the log files than may actually be included in them.