I am in the process of migrating my databases from SQL 2008 R2 to SQL 2014. I installed the SSDT and successfully migrated my SSIS Packages to the new server. The packages run fine within SSIS. I am now trying to set up the jobs that actually run these packages and have run into issues.
I have a default instance for SQL Server and an instance named test where I put copies of my production databases. Right now I am just working with the test instance. The Service account for both instances, the integration service account and the SQL Agents for both instances are using the same domain account. Lets call it mydomain\sqlsrv. I also have a login created for an additional account that is used in a credential. This is a domain account as well. Lets call it mydomain\ssis. I have a proxy created for SSIS Package Execution that uses this credential.
I exported scripts of the jobs on the 2008 server and ran them on the new server (changing the name of the server referenced in the script.) When I run those jobs, I get an error "The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'mydomain\SSIS'". The owner of the job is the SSIS account and it runs as the Proxy I set up. The job is set to use Windows Authentication.
If I change the job to use SQL Server Authentication and use the SA Account, it runs fine.
I've experimented with the permissions on the credential account and that hasn't made any difference. I started with the same permissions I had on the original SQL 2008R2 server which includes dbo role on MSDB & databases I am importing to and all the SQLAgent Roles on the MSDB Database. I added the credential account to the component per http://pramodkasi.blogspot.com/2014/07/connecting-to-integration-services.html.) That made no difference. In addition to that I have added the credential account as a member of the local administrators group. - again, that made no difference.
I've also tried creating the job from scratch and I get the same error. If Instead of telling it the Package Source of SQL Server, I change it to SSIS Package Store and select the package, then I get this error when I run it: "The SQL Server instance specified in SSIS service configuration is not present or is not available."
It has to be a permission somewhere - but where?