Error while trying to execute an ssis package in sql server agent job

Question

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: 16:34:44 Error: 2015-10-15 16:34:45.04 Code: 0xC0011007 Source: {068F0DFC-339B-4E9B-BFC8-EA73BF6AE23B} 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-10-15 16:34:45.04 Code: 0xC0011002 Source: {068F0DFC-339B-4E9B-BFC8-EA73BF6AE23B} Description: Failed to open package file "C:\Users\ssis\Documents\Visual Studio 2010\Projects\DemoProjectwithLinedLookup\DemoProjectwithLinedLookup\ImportDataMigration.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\ssis\Documents\Visual Studio 2010\Projects\DemoProjectwithLinedLookup\DemoProjectwithLinedLookup\ImportDataMigration.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\Users\ssis\Documents\Visual Studio 2010\Projects\DemoProjectwithLinedLookup\DemoProjectwithLinedLookup\ImportDataMigration.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: {068F0DFC-339B-4E9B-BFC8-EA73BF6AE23B} Started: 16:34:44 Finished: 16:34:45 Elapsed: 0.172 seconds. The package could not be found. The step failed.

Answer 1

Got this 0x80070005 error recently 2/2024 on a server and I'm going to talk about a server execution situation. Just to be clear this is a windows access issue, not some db signon type problem. If you dtexec without the full path you will get either the 32 bit or 64 dtexec and libraries depending on your path. The service account on your server running your package has to have access to the file path where dtexec and the libraries are and if it is using related software like the oracle client, the service account must have access to the oracle client software too. You can use "where dtexec" to see the dtexecs that are in your %path% . The service account that is running your package must have windows access to the binaries dtexec/ssis and other software (oracle, teradata) required by your package. If you are running your package on a new server/software, job management software setup this may be your problem.

Answer 2

Follow the below steps to resolve the issue.

On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.

In SQL Server Configuration Manager, click SQL Server Services.

In the details pane, right-click the name of the SQL Server Agent instance for which you want to change the service startup account, and click Properties. In the SQL Server Agent Properties dialog box, click the Log On tab, and select one of the following Log on as account types:

Select a Windows Built-in account if your jobs require resources from the local server only. For information about how to choose a Windows built-in account type, see Selecting an Account for the SQL Server Agent Service.

NOTE: This solution applies when you are placing the dtsx package in yur local machine and the SSIS IN 32- bit mode.

Answer 3

Make sure that the path to the SSIS package used in the step is accessible within the executing context - i.e. Beware of network paths, where you mean to point to local paths relative to the executing context.

Answer 4

I've seen many posts suggesting to create a proxy for the agent so your agent can access the package, but I have not seen any posts addressing password protection. If you are running a job between servers requiring login credentials, this will run fine on your desktop, but the agent will not execute. By default, the ProtectionLevel under security will be "DontSaveSensitive", meaning your passwords aren't saved. So when your agent goes to run the job, it can't login to the databases. In the Control Flow, change the Security ProtectionLevel to "EncryptAllWithPassword".

Go back into Management Studio and try to run your job. You will be prompted to enter the password. My error code suggested the need for a proxy, but this was the actual culprit in the end.

Answer 5

Let me boil that error down to what matters:

Executed as user: NT Service\SQLSERVERAGENT

Failed to open package file "Access is denied."

Your SQL Agent account does not have access to the package. You need to change the SQL Agent account to something that has access or use a proxy account. This is the number one beginner issue with SSIS / SQL Agent

The reason it works in BIDS is because you are running it as you, rather than SQLSERVERAGENT

It has nothing to do with 32/64 bit (at this stage)