I have a SQL Server 2012 where all databases are set up in instances.
I created a DTS package that accesses a table in a database, let's say MyDB\MyInstance and CSV files in a network folder.
Integration Services is NOT running in an instance.
My domain account doesn't have rights to login to Integration Services, so the DBA used his account and deployed the DTS package to Integration Services MSDB relying on the package for access and it runs from there without any issues. The DTS package uses my domain account to login to the database and access the table.
Then I created a SQL Job to run the package. It didn't run, of course. I create a proxy account to run the package. The proxy account uses the credentials of my domain account (which doesn't have access to SSIS, but the DBA temporarily made it a local admin on the SQL Server assuming that would give it the rights that it needed but that didn't work either. The error message states that it can't access the Integration Services database.
We gave the proxy SQL Agent operator role so we run the job with it.
The account running the SQL Agent for the instance is NT Service\SQLAgent$MyInstance
I don't know what else to try.
If there's any information missing please let me know. I apologize, there are a lot of moving parts.
Best Answer
In SQL Server 2012, you should typically use the SSIS Catalog (database name is SSISDB) for storing your packages for execution. You can still store them in MSDB, but this is not recommended.
Double-check your SQL Agent job that it is not trying to connect to the SSIS catalog to execute the package, in which case it will fail because A) you probably don't have access to SSISDB (it may not even be configured), and B) the package is not in SSISDB, but MSDB.
Check the agent job and confirm that the package source is set to SQL Server and not SSIS Catalog to verify you're trying to run from the correct location.
Also, if you can post the full details of the error message it will help to identify your root issue quicker.
Link