0

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.

  • 1
    If you check under MSDB>Security>Database Roles>"SSIS"Roles - you will want to ensure the agent (the account running the package) has one of the pre-defined roles for the enabling of running SSIS packages (Ex. SSISOperator). – rvsc48 Jan 14 at 17:57
  • I forgot to mention that. It has the Operator Role. – dzmr83 Jan 14 at 18:07
  • When you say "DTS Package", do you really mean DTS package or do you mean SSIS package? What, exactly, do you mean by "log into Integration Services"? You say that SSIS wasn't installed, but still you can execute that package? I'm trying to make heads and tails of your messages, but I keep getting confused as I read it... – Tibor Karaszi Jan 14 at 20:35
  • Yes, it's an SSIS package. It's also called DTS for historic reasons. I didn't say SSIS is not installed, but that it's not running in an instance. Sorry for the confusion. – dzmr83 Jan 15 at 4:06
0

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

  • You are right, the SSIS Catalog is not configured. The concern was that it requires Enable CLR Integration and I'm not sure how it will impact all other SQL instances in production on the server. Question: if configured, will it show up in SSIS next to File System and MSDB as a place to store the package? – dzmr83 Jan 15 at 4:11
  • Yes, the package source is set to SQL Server. I'll get the exact message, thanks. – dzmr83 Jan 15 at 4:13
  • FYI, I have never had this issue before when there are no SQL instances. Is the instances what's complicating things here? – dzmr83 Jan 15 at 4:15

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.