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

Question

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

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.

ssis

ssis-2012

asked on Stack Overflow Oct 15, 2015 by

Gowtham Ramamoorthy

4 Answers

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.

answered on Stack Overflow Oct 15, 2015 by

Gowtham Ramamoorthy • edited Apr 10, 2021 by

desertnaut

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)

answered on Stack Overflow Oct 16, 2015 by

Nick.McDermaid

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.

answered on Stack Overflow Sep 8, 2017 by

Andrew

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.

answered on Stack Overflow Dec 4, 2018 by

Ryan Collingwood • edited Apr 10, 2021 by

desertnaut

User contributions licensed under CC BY-SA 3.0