question

TarvinBrandie-5865 avatar image
0 Votes"
TarvinBrandie-5865 asked TarvinBrandie-5865 edited

SSIS "administrator" error on SQL Agent job step

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-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TarvinBrandie-5865 avatar image
0 Votes"
TarvinBrandie-5865 answered TarvinBrandie-5865 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @TarvinBrandie-5865 ,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.