I struck this error in one of my SSIS packages, I found lots of tips and suggestions online for the error but none of the solutions worked for me. So I want to add my solution to the mix so that it might give the next person searching an extra option to try.

Setup: I have a simple SSIS package that loads a couple of csv files into SQL Server tables and then calls stored procedures to clean and scrub the data. Everything works on my development server so I deploy to production by saving the package to the SQL Server and scheduling a job to run the package. (I’m using 2012 database engine and SSIS). But the job fails with the following error:

Could not load package “xxx” because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired).
The SQL statement that was issued has failed.

Investigation: My first thought is that this is some sort of permissions issue, and a lot of my search results seemed to suggest either that or a mistake in server name or other parameters.

-I was able to run the package manually in production so I reasoned that the agent service account might be the issue.

-Check that the SQL Agent service account has sufficient permissions to the various folders. I wasn’t able to get onto the server and use config manager for some reason so I used:

DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread
    'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
    'ObjectName', 
    @sn OUTPUT;
SELECT @sn;

-Check that the agent account had sufficient permissions to execute the stored procedures.

use [Database]
go
grant execute on object:: [procedure 1] to [agent service account]
grant execute on object:: [procedure 2] to [agent service account]
...

- Try again. Still not working!

- After much scratching of heads and comparing the failing job configuration with that of successful jobs it turned out that using the SQL Server’s fully qualified domain name in the Package configuration tab fixed the issue!

Conclusion: I don’t really know if this is a quirk of my current AD environment, or a requirement of SSIS 2012. It worked for me, it might work for you. If anyone can shed any further light on what’s going on here – please do.