Running SSIS as SQL agent

SSIS and Import/Export
1

Can someone help me fix this issue please.
I am trying to run ssis package (NasirETL.dtsx) from a network drive. I have created an SQL job to run on a specific time.

I am not sure what this means as I only have basic knowledge of SQL server. What my understanding is when I run this package from Visual studio it works. However, when I run it from SQL agent job it gives me below issues.
Whenever I run this job it gives be the following error:

Executed as user: PROTOCALL\theGrayMan. Microsoft (R) SQL Server Execute Package Utility Version .......... for 64-bit Copyright (C) 2019 Microsoft. All rights reserved.
Started: 12:12:48 Error: 2022-07-27 12:12:48.31 Code: 0xC0011007 Source: {}
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: 2022-07-27 12:12:48.31 Code: 0xC0011002 Source: {}
Description: Failed to open package file "\theGrayMan\m$\All$\ELT\NasirETL.dtsx" due to error 0x80070003 "The system cannot find the path 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 "\theGrayMan\m$\All$\ELT\NasirETL.dtsx" because of error 0xC0011002.
Description: Failed to open package file "\theGrayMan\m$\All$\ELT\NasirETL.dtsx" due to error 0x80070003 "The system cannot find the path 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: {} Started: 12:12:48 Finished: 12:12:48 Elapsed: 0.031 seconds. The package could not be found. The step failed.

2

What account are the different sql services running under?

3

The service account won't have access to the admin share. You need to create a shared folder and grant permissions to that shared folder to the service or proxy account running the SSIS job.

The reference should be something like: \theGrayMan\ELT\NasirETL.dtsx

4

I have created credentials and proxy. Now saying username and password are incorrect.
I have granted read and write access on that shared folder.

5

If you are still using the hidden share - it isn't going to work. If you are now using a credential and proxy and receiving a username/password failure then the proxy account does not have the correct username or password.

6

I have double checked username and password. Username is same email address as job owner and password is same as the one I log into PC. Do I need to create a new password if yes how do I create new password.

7

:eyes: a different AD user but using your password?

Is this an actual AD user or a sql user? Or what?

8

I have same access as sysadmin. So used myself for test however I have used sysadmin for job.

I have changed the hidden location on my flat file source location and it has worked. The issues isn't where the path of package file is (
\theGrayMan\m$\All$\ELT\NasirETL.dtsx)

Now it's reading from C drive of a server.

9

@yosiasz and @jeffw8713 thanks alot for your help

1 Like
10

that is very worrisome that you are using sysadmin. would not recommend it.

11

Should I create another SQL user and grant read only to my location and then use proxy.
What do you think @yosiasz
Is that a good approach or would you like to suggest something

12

are you in a windows environment with active directory?

13

Yes,

14

Is the proxy user you used for the sql job that runs the ssis package or the account the service is running as a legit AD user?

15

The source files path/files need to be stored on the same as your “server” C drive. That's an easy fix to the above problem.
If there is another easy fix, let me know.