November 5, 2021 at 6:59 pm
For "reasons," our corporate office is requiring us to move all our SSIS packages to the SAN share on our servers and call the package with a Command Operating System task. It's incredibly frustrating but we've done it before on SQL 2005, so it's not unknown to us. Unfortunately, SSIS now requires administrator access to the server to be able to run (a Microsoft thing). Our service accounts are added to the local admin account and I'm pretty sure I got the command line correct, but I'm still getting the "must be an administrator" error whether I'm running the job manually on my desktop or on the server.
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?
November 5, 2021 at 7:43 pm
Too bad you didn't migrate to the catalog - that would take care of these kinds of issues as well as provide additional features that are not available for package deployment and files.
With that said - I see your command line using a drive letter. A SAN share would be a mapped drive and the service/proxy account running the agent job would not know anything about a mapped drive. Using a drive letter from a command exec job is looking at the local server where that job is running and either does not have access to that location - or that location doesn't exist for that account.
I am not sure what you mean about SSIS needing administrator access. Are you saying that SSIS needs sysadmin access - or local administrator access or something else? That is not anything I have seen or heard - and it isn't required for the catalog.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 5, 2021 at 11:41 pm
No admin required - that is just bad clarification - just required priv required on DCOM which can be given to a non admin user/group.
see https://dba.stackexchange.com/questions/77320/how-do-i-grant-the-privilege-of-connecting-to-ssis for how to fix it.
but I wonder why you using /server - if you are now required to have your packages on the filesystem then using /DTS and /SERVER are no longer applicable and you should instead be using /FILE fullpath_to_dtsx
November 8, 2021 at 11:46 am
I'm using drive letters because we're using a folder on the local-to-the-server SAN, not a NAS. The last time we tried using a NAS share, things kept breaking every time there was a network blip.
I remember using / DTS, didn't even think to try /FILE. I'll do that, see if it makes a difference.
Regarding administrator access, I know for a fact that I can't sign into SSIS on the server since SQL 2012 without being a member of the server administrators group (see the error message above). Our office segregates our permissions so that our normal NTIDs don't have server access and can't be used by hackers. Because of that, when we try to open up SSIS in SSMS on our desktops, we get an error very similar to the above.
We have to have a special account to sign into servers (members of the administrators group) which we can use to sign into SSIS. Basically, we set up shortcuts to SSMS as signing in with a different user, then use our admin accounts to sign in when we need access to SSIS from our desktop without having to sign into the server itself. Before SQL 2012, we could use our normal accounts. It started with 2012. Sysadmin on SQL doesn't cut it.
EDIT: I will see if we can potentially do the DCOM thing for our non-administrator accounts to have access in SSMS. But that won't resolve the job issue because I did run it with an account that has permissions in DCOM and it still popped that error message.
November 8, 2021 at 11:48 am
No go with /FILE.
Executed as user: <ServiceAccount>. Microsoft (R) SQL Server Execute Package Utility Version 15.0.4102.2 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. The File option cannot be specified with the DTS, SQL, Server, Project, ISServer, User, or Password options. Process Exit Code 6. The step failed.
Any other suggestions?
EDIT: I forgot to mention something regarding the command line. I copied it from the Integration Services step, then changed SQL to DTS. The /SERVER stuff is all part of the connection string. It should be there.
November 8, 2021 at 12:59 pm
Command line is:
dtexec.exe /DTS "\"G:\SSIS\Folder\MyPackage\"" /SERVER MyInstance /CONFIGFILE "\"E:\Folder\Configfiles\MyPackage.dtsConfig\"" /CHECKPOINTING OFF /REPORTING E
change to:
dtexec.exe /FILE "\"G:\SSIS\Folder\MyPackage.dtsx\"" /CONFIGFILE "\"E:\Folder\Configfiles\MyPackage.dtsConfig\"" /CHECKPOINTING OFF /REPORTING E
November 8, 2021 at 1:00 pm
Just verified the DCOM permissions add doesn't allow me to connect to SSIS via SSMS with my non-administrator account.
November 8, 2021 at 1:08 pm
Command line is:
dtexec.exe /DTS "\"G:\SSIS\Folder\MyPackage\"" /SERVER MyInstance /CONFIGFILE "\"E:\Folder\Configfiles\MyPackage.dtsConfig\"" /CHECKPOINTING OFF /REPORTING E
change to:
dtexec.exe /FILE "\"G:\SSIS\Folder\MyPackage.dtsx\"" /CONFIGFILE "\"E:\Folder\Configfiles\MyPackage.dtsConfig\"" /CHECKPOINTING OFF /REPORTING E
Nope. New error.
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2021-11-08 07:03:06.27 Code: 0xC0011002 Source: {D04692AD-1277-4DEE-B8B8-EAF50D05CAB1} Description: Failed to open package file "G:\SSIS\Folder\MyPackage" due to error 0x800C0006 "The system cannot locate the object specified.". This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format. End Error Could not load package "G:\SSIS\Folder\MyPackage" because of error 0xC0011002. Description: Failed to open package file "G:\SSIS\Folder\MyPackage" due to error 0x800C0006 "The system cannot locate the object specified.". This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format. Source: {D04692AD-1277-4DEE-B8B8-EAF50D05CAB1} Started: 7:03:06 AM Finished: 7:03:06 AM Elapsed: 0.031 seconds. Process Exit Code 4. The step failed.
The package is at that location. The service account can actually reach it. The package is a working package I took directly from SQL Server SSIS (MSDB, not SSIS Catalog). So I'm taking this entire error message with a pile of salt. I don't think /FILE is the way to go.
November 8, 2021 at 1:31 pm
/FILE is the way to go if what you posted on originally is indeed what is being requested and done.
as you can see on the top examples here https://docs.microsoft.com/en-us/sql/integration-services/packages/dtexec-utility?view=sql-server-ver15#bit
the command is "dtexec /F(ile) mypackage.dtsx ...." - and I can assure you of that as I have still a way high number of packages being executed like that.
regarding the error - lots of things can cause it and not easy to determine without access to it - but can you open the package from the filesystem within Visual studio - if the package is valid it will open it - if not it will through some errors. Fact they were on msdb and saved to filesystem may cause some issues.
might help if you can post here the first 4 or 5 lines of the package (replacing anything that may be username/pcname)
in terms of process you should not be extracting from MSDB and saving to file - y0u should really use your VS solution to build the package according to your target SQL SSIS version and deploy/copy that .dtsx file to the folder where you will be executing it from.
on the integration services part - once you start using filesystem (or SSIS catalog) you can kind of forget that that other thing ever existed as it will not be used.
and finally - why that requirement to have the packages on the filesystem? its unsafe, open to abuse, and in most cases should require that the packages are encrypted (which leads to other issues!!!).
running from command line I can accept (in some cases) - but running on filesystem only on very specific cases - mostly where the packages are built dynamically at runtime.
November 8, 2021 at 5:50 pm
I agree with you on the security issues, but "the law" has been handed down for reasons I can't go into on a public forum. My hands are tied.
Are you using config files on the packages you're calling from your jobs? Are the packages located on the server's SAN drive or an external NAS share? Do you call them with drive letter or share name?
I'm trying to get more insight so I can verify the similarities as I continue trying out different scenarios to get this working.
November 8, 2021 at 6:58 pm
we have both (on top of those that are on SSIS catalog)
in some servers the packages are stored on a SAN drive attached to the server (with a drive letter assigned). On these the packages are referenced by using the drive:\folder
on others the packages are on a network share and are accessed using \\sharename\foldername\package.dtsx
in either case we use config files also - again referenced with either method.
your particular case does look like an invalid xml file for the dtexec you are using (package created for an older version perhaps?).
without seeing the package and opening it with VS to see if it complains or what it changes when you save it its difficult to know exactly what the issue is - likely related to being extracted from MSDB. which leads to - how did you extract them from MSDB?
November 8, 2021 at 8:16 pm
Okay. FINALLY. Apparently just editing the command line from the Integration Services job step type wasn't going to do it.
I needed to revert to the old version of the job, open up the Integration Services Job step, change the package location from SQL Server to File System, navigate to the new package location and choose the package, verify the configuration file was still chosen correctly, go to the Command Line tab (which has the /FILE as you said) and then switch it to an Operating system job step type.
This properly populated the job with everything but the dtexec.exe in front. Once I added that, saved all the work, I was able to run the job without errors.
But for some reason, me manually entering the command with the /FILE wasn't working. So there must have been something in the manual update the job didn't like but it liked it just fine when SQL did all the editing work.
<GAH>
November 8, 2021 at 8:26 pm
something minor for sure - even a single quote vs double quote could cause the package not to be opened.
glad you sorted it out
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply