SQL Server Agent and SSIS

I created a SSIS package that litterally just export a view onto a server. The database, ssis and location are all on the same server. When I run the SSIS package in SSIS works perfectly. When I run it in SQL it doesnt work. I have read way to many articles trying to figure out how to fix this. The best was something about a proxy. I really just want to run the package through as a JOb through SQL Server Agent. Any directions would be great. 

Message
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  3:15:00 PM  Error: 2015-03-05 15:15:00.41     Code: 0xC0011007     Source: {A9456201-8A74-44C9-839F-1CD3CC02637F}      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: 2015-03-05 15:15:00.41     Code: 0xC0011002     Source: {A9456201-8A74-44C9-839F-1CD3CC02637F}      Description: Failed to open package file 
"C:\Users\lasadmin\Documents\Visual Studio 2010\Projects\Integration Services Project1\Integration Services Project1\Package.dtsx"
 due to error 0x80070005 "Access is denied.".  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 "C:\Users\Documents\Visual Studio 2010\Projects\Integration Services Project1\Integration Services Project1\Package.dtsx" because of error 0xC0011002.  Description: Failed to open package file "C:\Users\Documents\Visual Studio 2010\Projects\Integration Services Project1\Integration Services Project1\Package.dtsx" due to error 0x80070005 "Access is denied.".  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: {A9456201-8A74-44C9-839F-1CD3CC02637F}  Started:  3:15:00 PM  Finished: 3:15:00 PM  Elapsed:  0.015 seconds.  The package could not be found.  The step failed.

March 5th, 2015 4:23pm

The error pretty much eloquent enough to conclude Executed as user: NT Service\SQLSERVERAGENT has 0 rights.

As you mentioned already, you do need to use a proxy account to run the SSIS step in the Agent job

Free Windows Admin Tool Kit Click here and download it now
March 5th, 2015 4:48pm

I will take a look and try it out but from what I read isnt NT Service\SQLSERVERAGENT a system admin account by default?
March 5th, 2015 6:53pm

For the database engine yes, but not the file system: "C:\Users\lasadmin\Documents\Visual Studio 2010\Projects\Integration Services Project1\Integration Services Project1\Package.dtsx"All the accounts that are NT Service based they do not have rights to access files by default.

PS: When I said 0 rights it was in regards to file system, it looks like an overstatement.

Free Windows Admin Tool Kit Click here and download it now
March 5th, 2015 9:41pm

The package path starts with C:\Users\Documents\Visual Studio 2010\..

Are you sure the the package resides in the same folder in the server where you've configured the job? If package is in a remote server you should be using UNC path (//MachineName/...) to point to the package

Another thing to note is regarding permissions on folder where package resides. the executing account should have access to the path where package is present. Also any other paths/files/ applications which package tries to use within its logic should also be placed in folder where account has access

March 6th, 2015 12:30am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics