MSSQL Scheduled Task to run SSIS Package Issue

jtech 26

I have an SSIS Package that I am trying to schedule to run Daily via a new job that I created in MSSQL. The issue is that when I set the path to my SSIS Package, an error pops up saying the following:

Invalid package path "\servername\mySSISPackageLocation\Package.dtsx" on the server "localhost". It cannot display the configuration of the package.

From the hours of research on my issue thus far, it appears that most people attribute my error as MSSQL not having adequate privilege's to the folders (location) where I have the SSIS Package stored. However, I can't find any instructions on how to setup the permissions for MSSQL to properly access it...? Both the MSSQL & SSIS instances I have are just for testing, so I've set about everything up in both Windows and MSSQL to have "Full Control" but still with no success.

Below are some of the things I have tried:

Shared the root folder with my SSIS Package, added "Everyone" to have full control and set the security permissions on the folder to the only account I have for Windows on this machine to full control as well.
Added "NT Service\MSSQLSERVER" & "NT Service\Winmgmt" to the root folder that I previously shared in the above step and set full control access on it.
Set most all "Logins" within MSSMS to have the role of "sysadmin".
Closed out of all SQL applications and restarted the SQL Services in Windows after each above change.
Have rebooted my machine several times as well.

Not really sure what else I am missing, and I am a novice at this, but any guidance or help would be greatly appreciated!

5 answers

Erland Sommarskog 115.9K Reputation points MVP

2023-01-06T18:37:06.277+00:00

Keep in mind that the accounts NT Service\MSSQLSERVER etc are local to the machine, so they cannot be granted permissions on a folder share. The other machine knows nothing about the accounts on the SQL Server machine.

You can grant permission to DOMAIN\MACHINE$, that is for the machine account for the machine where SQL Server runs, but this may be dubious from a security perspective.

A better alternative would be to run SQL Server under a domain account, which could be a gMSA.
Please sign in to rate this answer.

1 person found this answer helpful.
jtech 26 Reputation points

2023-01-09T16:54:53.29+00:00

Hi Erland,

Thanks for your response! Although that may work for me test, to then transfer it into live production would be an issue (as you mentioned, haha! I don't think the Security guys want me using the domain Admin, so the idea of the other guys post of using a Proxy User sounds more practical, well, except that I can't get that to work now either, boo! :(
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
ZoeHui-MSFT 40,051 Reputation points

2023-01-09T02:33:18.657+00:00

Hi @jtech ，

How do you set the step of your job?

Are you using file system or sql server in the package source?

It seems like a permission issue, try to set proxy account which could access the server to run the job.

Reference: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
jtech 26 Reputation points

2023-01-09T16:42:59.923+00:00

Hey Zoe,

I have a response, but the website won't let me type a comment. This is a test in another browser to see what the issue is with me being not able to post.

jtech 26 Reputation points

2023-01-09T16:46:21.807+00:00

Hey Zoe,

It's not my browser, but something in the syntax of the post it doesn't like (I guess). I am attaching a photo of what I wrote instead. Sorry!

Erland Sommarskog 115.9K Reputation points MVP

2023-01-09T22:09:37.19+00:00

For some reason, your post was marked as Deleted. I took the liberty to undelete it.

jtech 26 Reputation points

2023-01-10T19:07:22.043+00:00

I deleted it because by making my reply to Zoe had to go into this forum as a "Answer" which then auto-locked the thread and marked it as Completed. It's not completed, so I deleted it to re-open this thread, and then discovered that it was something with the syntax that this web forum didn't like, so I sent an image instead. I would check the logs of this thread to learn what the error was as to why it wouldn't let me post a reply to Zoe to get to root cause. This was very odd.

Erland Sommarskog 115.9K Reputation points MVP

2023-01-10T22:06:43.077+00:00

OK, I see. I don't think I've heard of that behaviour before.

Although, the way the forum is intended to work is that Answers are for answers to the original question. So if you as the original poster want to supply more information in response to an Answer, you should make that as a Comment, not as an Answer. You would only post an Answer yourself, if you found te solution yourself.

Yeah, I know, I don't always follow this myself. When there are many level of comments, the input box starts to get small. And then there is this 1600-char limit to comments.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
jtech 26 Reputation points

2023-01-09T16:41:27.637+00:00
Hey Zoe,

For the step of my job, I was following an article that said to use a Package Source of "SSIS Catalog" and when trying to browse to the path nothing would show up in the list to even select, so I would manually enter the path, but upon doing so, the error would occur. After your post here, I then switched the package source to "File System", and from there it did let me browse to the SSIS Package.dtsx that I want the job to run, but then upon running I get errors that say "Login failed for user 'NT Service\SSISScaleOutMaster150'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]".

In trying to troubleshoot this error further, I have performed all of the following, but to no success:

Followed your link's instructions to create a proxy user, setup in windows, set permissions in MSSQL, and set permissions on the Windows folder where my SSIS Package is located: all via https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

Followed another links instructions to set appropriate permissions for the new proxy account I created per the step above; using this link: https://learn.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service?view=sql-server-ver16

Per the error, saying that the account of "NT SERVICE\SSISScaleOutMaster150" could not find a login, I added this account to have full access to both the folders location for my SSIS Package, as well as the SSIS Service from the step above this one.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
ZoeHui-MSFT 40,051 Reputation points

2023-01-10T02:05:35.93+00:00

Hi @jtech ，

Please first check that you have installed SSIS as said here.

And then open SSMS, connect to this instance, right-click on logins -> New login to add 'Add NT Service\SSISScaleOutMaster150' to SQL Server login.

Restart the server and run the job for a try.
Please sign in to rate this answer.
jtech 26 Reputation points

2023-01-12T15:24:53.0533333+00:00

Hi Zoe,

The MSSQL Installation I have does have these features installed (as per your link). I did add the NT Service\SSISScaleOutMaster150 to the Login's folder, but not sure what all permissions or detailed configuration you were expecting me to set after that, so I gave it 'sysadmin' rights, and made it an owner of my SSIS Package. I then restarted the Server, pulled up my job, but the exact same error is returned.

If there is something more I needed to do to add that new login to "SQL Server login" as you asked, then I would need to know more details/instructions about what you mean, and/or how to configure as I am too new to all of this to piece deep details together from a general recommendation/statement. I really do appreciate your help so far, and the ideas/suggestions you have had, but just wanted to remind you that I am a novice at this so small details that others might know to just do, I probably won't know.

Thanks again for your help so far.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Guoxiong 8,206 Reputation points

2023-01-12T19:18:45.4133333+00:00

You might need to deploy your SSIS project or package to the SQL server through the Visual Studio. Check this article "Deploy and Schedule an SQL Server Integration Services SSIS Package Step by Step".
Please sign in to rate this answer.
jtech 26 Reputation points

2023-01-18T19:38:28.2233333+00:00

Hi Guoxiong,

I had not performed the steps on the article you referenced here, so I logged in and was able to get the SSISDB Catalog configured in SSMS. I then followed the steps to deploy the SSIS Package from the Visual Studio, but when clicking on "Browse" and selecting the SSISDB folder, I can't click "OK" because the button is greyed out - like it wants/needs something more...?

Is there another step or something I might be missing?

Thanks!

Jeremy

Guoxiong 8,206 Reputation points

2023-01-18T21:03:18.76+00:00

Do you deploy an SSIS project or a package? If you want to deploy the package without deploying the whole project, you must have SQL Server 2016 (or higher) Integration Services (SSIS) installed.

You also can deploy the project or the package using Integration Services Catalog --> SSISDB --> Your_Folder.

Make sure your account has the admin right in SSISDB. If you still have an issue, can you attach a screenshot?

jtech 26 Reputation points

2023-01-19T14:03:36.97+00:00

I downloaded and installed the Microsoft Visual Studio 2022. From there, I opened that software and created my SSIS Package, configured it, and ran it without an issue. It successfully dumps all my MySQL Test database table information into MSSQL and works great. The version of MSSQL I have is 2019, and I am using v18 of the Management Studio.

In previous comments from the community, I have gone/dug pretty extensively into the user permissions side of things to make sure that this is not the issue by setting up a proxy user and making sure the account has access to login to SQL.

When you say to make sure my account has admin rights in SSISDB, I am not sure what you mean by that? If I right-click on SSISDB and look for anything to do with rights or access to this, I don't really see anything...?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

MSSQL Scheduled Task to run SSIS Package Issue

5 answers

Your answer