SSIS "administrator" error on SQL Agent job step

Tarvin, Brandie 96 Reputation points
2021-11-08T10:56:48.21+00:00

Our corporate SQL team won't let us use SSIS packages on SQL Server or in SSIS Catalogs. We have to put them on a SAN share and call them via an Operating system (CmdExec) job step. Unfortunately, we can't seem to get the step to work. When I run the job manually to test it out, I keep getting a "must be a member of server admin group" message.

It doesn't matter if I'm doing it on my desktop with my normal account or logged into the server with an administrator account. Both ways the job fails when calling it manually.

Command line is: dtexec.exe /DTS "\"G:\SSIS\Folder\MyPackage\"" /SERVER MyInstance /CONFIGFILE "\"E:\Folder\Configfiles\MyPackage.dtsConfig\"" /CHECKPOINTING OFF /REPORTING E

Error is: Description: Connecting to the Integration Services service on the computer "MyInstance" failed with the following error: "Access is denied." By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service. Source: Started: 1:48:11 PM Finished: 1:48:11 PM Elapsed: 0.031 seconds. Process Exit Code 5. The step failed.

At the very least, it should have worked when calling it on the server. I checked the DCOM config and the administrators group has all the right access to SSIS on the server. I checked Computer Management and our group is in the administrators group.

Any thoughts as to what I could possibly be missing or have gotten wrong here?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,630 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions
0 comments
Accepted answer
  1. Tarvin, Brandie 96 Reputation points
    2021-11-09T10:40:00.023+00:00

    So I posted an update yesterday, but it didn't seem to have taken.

    After spending all day trying different variations on /DTS and /FILE, I had to revert to the old job step type (Integration Services), change the connection to File System, point to the package in the folder, verify the config file information was still correct, navigate to the Command Line tab, THEN switch the job step type to Operating system and add dtexec.exe to the beginning.

    After I did all that, it finally worked as expected.

    I think my manual copy-paste then edit must have done something odd and made it unreadable to SQL. At least I know going forward what to do to all the other jobs.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,426 Reputation points
    2021-11-09T06:00:29.823+00:00

    Hi @Tarvin, Brandie ,

    Check below to see if it is helpful.

    1.Check if you may have missed a step in the configuration setup - Check Eliminating the "Access Is Denied" Error.

    2.To connect directly to an instance of the legacy Integration Services Service, you have to use the version of SQL Server Management Studio (SSMS) aligned with the version of SQL Server on which the Integration Services Service is running. For example, to connect to the legacy Integration Services Service running on an instance of SQL Server 2016, you have to use the version of SSMS released for SQL Server 2016.

    Check the version of your SSIS and SSMS to see if they are match.

    release-notes-ssms

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments