Error while executing a SQL Agent job

Question

Hi,

I am done with the development of a package. Now I am trying to execute the package through SQL Agent. I created a job. But it is throwing the following error message when the job runs:

Microsoft (R) SQL Server Execute Package Utility
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  11:32:00 AM
Error: 2010-01-19 11:32:01.79
   Code: 0xC001401E
   Source: Moffet_Install_Uninstalls Connection manager "Moffet_Installs"
   Description: The file name "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\Data Files\Moffet\Installs.txt" specified in the connection was not valid.
End Error
Error: 2010-01-19 11:32:01.79
   Code: 0xC001401D
   Source: Moffet_Install_Uninstalls
   Description: Connection "Moffet_Installs" failed validation.
End Error
Error: 2010-01-19 11:32:01.79
   Code: 0xC001401E
   Source: Moffet_Install_Uninstalls Connection manager "Moffet_Uninstalls"
   Description: The file name "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\Data Files\Moffet\Uninstalls.txt" specified in the connection was not valid.
End Error
Error: 2010-01-19 11:32:01.79
   Code: 0xC001401D
   Source: Moffet_Install_Uninstalls
   Description: Connection "Moffet_Uninstalls" failed validation.
End Error
Error: 2010-01-19 11:32:01.83
   Code: 0xC001401E
   Source: Moffet_Install_Uninstalls Connection manager "Moffet_Installs"
   Description: The file name "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\Data Files\Moffet\Installs.txt" specified in the connection was not valid.
End Error
Error: 2010-01-19 11:32:01.83
   Code: 0xC0202070
   Source: Moffet_Install_Uninstalls Connection manager "Moffet_Installs"
   Description: The file name property is not valid. The file name is a device or contains invalid characters.
End Error
Error: 2010-01-19 11:32:01.88
   Code: 0xC001401E
   Source: Moffet_Install_Uninstalls Connection manager "Moffet_Uninstalls"
   Description: The file name "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\Data Files\Moffet\Uninstalls.txt" specified in the connection was not valid.
End Error
Error: 2010-01-19 11:32:01.88
   Code: 0xC0202070
   Source: Moffet_Install_Uninstalls Connection manager "Moffet_Uninstalls"
   Description: The file name property is not valid. The file name is a device or contains invalid characters.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  11:32:00 AM
Finished: 11:32:02 AM
Elapsed:  1.5 seconds

Answer 1

Have you executed the package through BIDS?
Are the text files (Install.txt and UnInstall.txt) exists at the location mentioned in the error message?
How are you creating the flat file connection manager?

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 2

1- it says that ...
Description: The file name "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\Data Files\Moffet\Uninstalls.txt" specified in the connection was not valid.
..... are your files in the right locations

Questions:
Q1: How are you calling the package in the job ? whats the code? are you using DTexec?
Q2: As nitesh mentioned is it working in BIDS?
Q3: you do have 2 connection flat file managers? right?

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)

Answer 3

If your SQL job is set up on a server (say Server-A) other than the one where the flat file is residing (your local desktop I guess???), the SQL job will try to look for this on the fixed path provided on Server-A and not on the desktop. I'd suggest to make this data folder a shared folder and then use the UNC path in the connection string of the FLat File connection manager.

That should resolve the issue.


Cheers!!
Muqadder.

Answer 4

Thanks for your suggestions. I had made a mistake in the path of the package, while scheduling in SQL Agent.
Now I get the following error.

Microsoft (R) SQL Server Execute Package Utility
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  4:21:01 AM
Error: 2010-01-20 04:21:01.71
   Code: 0xC0011007
   Source: {B00DAB70-AC53-4899-B2E2-767800BA689C}
   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: 2010-01-20 04:21:01.71
   Code: 0xC0011002
   Source: {B00DAB70-AC53-4899-B2E2-767800BA689C}
   Description: Failed to open package file "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\From Storm\Moffet_Install_Uninstalls\Moffet_Install_Uninstalls\Moffet_Install_Uninstalls.dtsx" due to error 0x80070005 "Access is denied.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\From Storm\Moffet_Install_Uninstalls\Moffet_Install_Uninstalls\Moffet_Install_Uninstalls.dtsx" because of error 0xC0011002.
Description: Failed to open package file "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\From Storm\Moffet_Install_Uninstalls\Moffet_Install_Uninstalls\Moffet_Install_Uninstalls.dtsx" due to error 0x80070005 "Access is denied.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
Source: {B00DAB70-AC53-4899-B2E2-767800BA689C}
Started:  4:21:01 AM
Finished: 4:21:01 AM
Elapsed:  0.703 seconds

This package has been running in BIDS. But is giving this error message wen scheduled through SQL Agent.

Answer 5

dose the package work in BIDS? if so
then now you still have a access problem
i am assuming that the job owner/agent does not have access to the folder where the package is located.

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)

Answer 6

In the package properties in BIDS, is the PackageProtectionLevel set to "DontSaveSensitive"???? If not, try setting this value in BIDS and then run the job to see if executes successfully.



Cheers!!
Muqadder.

Answer 7

Hi,

I changed the package property as you had mentioned. But amm still facing the same error. The job owner, SQL Server agent's account are all the same only. I am using the same login account for all. Infact, when I copy paste the path given in the log file "C:\Documents and Settings\root\My Documents\Visual Studio 2008\Projects\From Storm\Moffet_Install_Uninstalls\Moffet_Install_Uninstalls\Moffet_Install_Uninstalls.dtsx" in Start->Run, it opens the Execute Package Utility, from where I am able to execute the package. Only the scheduled job is having the issue.

Answer 8

When you say only scheduled Job is having the issue, then I would suggest two things to check:

1.) With what user is the package running under Agnet,does he have sufficient permissions to run the package and access the folder path.

2.) Also check inside the job step, with what user is the job step running and see whether there is permission issue with the user who has been configured for Job Step( not for the entire job)

Also to make sure it is a permission issue, if possible can you give access to every to the folder where the files exists and then run the package with Agent.

---

Raju

Answer 9

Thanks Raju. The package's owner had been a different user, and the SQL Server Agent Service has been running in a different account. Now only I opened the Services and saw the user behind the SQL Server Agent Service. I gave full permission to the folder for all users, and now the job is running successfully. Thanks everyone for the guidance.

Answer 10

Whichever step of package is throwing this error try to run that step as MMD Proxy (Job Properties - <Job name> ->> Steps ->> Job Step Properties -<stepname> ->> General ->> Run as: ->> MDDM Proxy)