Running SSIS Package in scheduled job
-
Hi,
I'm trying to run SSIS package stored on my local PC through SQL Server Agent job , and it always fails.
When run through my Login, it's always success.
Below is the error I get: Executed as user: DESKTOP\sa. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:45:56 AM Error: 2018-10-03 10:45:56.69 Code: 0xC0011007 Source: {BE221908-99A4-4707-8287-0B5FEE969825} 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: 2018-10-03 10:45:56.69 Code: 0xC0011002 Source: {BE221908-99A4-4707-8287-0B5FEE969825} Description: Failed to open package file "H:\SSIS\ssPackage.dtsx" due to error 0x80070003 "The system cannot find the path specified.". 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 because of error 0xC0011002. Description: Failed to open package file due to error 0x80070003 "The system cannot find the path specified.". 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: {BE221908-99A4-4707-8287-0B5FEE969825} Started: 10:45:56 AM Finished: 10:45:56 AM Elapsed: 0.016 seconds. The package could not be found. The step failed.
Please help.
I'm using 64-bit operating system.
Thank you!
Sindhu
-
Sindhu
Is H a physical drive on the computer on which the job runs, or is it a mapped network drive, or is it a drive on another computer? What account does SQL Server Agent run under?
John
-
Hi John,
Yes, H is the physical drive . SQL Server Agent run as [sa] username. Please help, as i have to manually run this every time.Thank you.
Regards,
Sindhu -
Yes, but is it a physical drive on the same computer that the job runs on, or is it a drive on the computer on which you're developing or storing the package? Does the DESKTOP\sa account have the necessary NTFS permissions to open it from that location?
John
-
Hi,
It is a drive on the computer on which I'm developing and storing the package. May i know how to give permission to sql agent user to access that folder?
-
Either copy the file to a location on the server, or create a share on the computer where the package is stored, and put that in your job step. The first option is easier.
John
-
Hi John, I tried copying file on to server using IMPORT PACKAGE , but i'm stuck about the settings and location to where the file should point to ?
Can you please share steps to copy package to server. That will be of great help.Thank you in advance
-
(1) Find a suitable folder on the server to which the SQL Server Agent account has at least read permission
(2) Copy the .dtsx file to that folder
(3) Amend the job step to point to the file in the new location
(4) Run the jobJohn
-
Hi John,
Thanks for your help.
When I tried to locate the file it is visible in the server location, but i am not sure how that location can be copied on to the file system in sql server agent job, can you help please?
\DESKTOP \C:\\MSSQLSEVER\100.....\Package1.dtsx, it looks incorrect location to me.
Regards,
Sindhu -
To pick up on John's point, you need to copy the dtsx package to the server itself to a folder which would then be accessible by SQL Agent, rather than trying to access the file which is on local machine.
This would also mean any time you update the package you would need to copy it from your local environment to the server folder.
Another option is to deploy the package to the server. This article should give you some advice.
https://docs.microsoft.com/en-us/sql/integration-services/packages/deploy-integration-services-ssis-projects-and-packages?view=sql-server-2017This link further explains then running packages from SQL Agent.
https://docs.microsoft.com/en-us/sql/integration-services/packages/sql-server-agent-jobs-for-packages?view=sql-server-2017Thanks
Mark -
For starters, SQL Server is not running under the SA account.
You need to look at the SQL Server Service, and determine what account that is running under.
You will then need to make sure that this account has the proper permissions to the folder where the package is stored.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
You would have much better success at running (and managing) your package(s) if you implemented the Integration Services Catalog. This would require changing your package to a project deployment method - but that would be a small change.
Once you have implemented the catalog - you would then deploy your project to the catalog and setup SQL Server Agent to run the package from the catalog.
With your current setup - you are attempting to run a package from the H:\ drive which is most likely not a local drive to that machine. It is probably a mapped drive that is available only to your account. The SQL Server instance would not have access to that drive - nor would it have the necessary permissions.
Try moving the package to a location on the C:\ drive (create a new folder) - and then setup the agent job to run the package from that drive. Modify the permissions on the folder and add the service SID account (NT SERVICE\MSSQLSERVER) to that folder and give it full control. To get the service SID account - you need to modify the permission lookup to look at the local machine instead of the domain.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
A lot of people are missing a major point here. I don't think SQL Server will run off a file share per say. SQL Server needs to be configured for "File System". My office had to do this with 2k8 and I'm pretty sure it has to be done with 2012. Here's a link to where you can find the actual SSIS config file. https://docs.microsoft.com/en-us/sql/integration-services/configuring-the-integration-services-service-ssis-service?view=sql-server-2014, adjusting for version in the path name, see if you can find the information for how to get SSIS to access the file share.
Then you need to actually load the package via the GUI into the File System folder, which does end up being an actual file location on the SAN but not necessarily a share. And yes, the Agent service account needs access to that location in order for it to work.
-
Many thanks for all information.
I still have problems doing this.
I have created stored procedure and run job through SQL Agent.
Regards,
Sindhu - sindhupavani123 - Monday, October 15, 2018 4:38 AM
How is the stored procedure assisting you in running your SSIS package?
I've never done something like that before, so am very interested in knowing the answer.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply