Run ssis package in agent job while both are on separate machines

0
votes

I am trying to run ssis package through agent jobs. The packages and the sql server are on different machines. When the agent job tries to run ssis package step the job fails with following error:

Executed as user: Domain\username. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 1:42:42 AM Error: 2020-01-23 01:42:42.49 Code: 0xC0011007
Source: {07BB41B6-D0DF-4349-B9AE-498397D214D7} 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: 2020-01-23 01:42:42.49 Code: 0xC0011002 Source: {07BB41B6-D0DF-4349-B9AE-498397D214D7} Description: Failed to open package file "D:\ssis_packages\package.dtsx" due to error 0x80070003 "The system cannot find the path specified.". 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 "D:\ssis_packages\package.dtsx" because of error 0xC0011002. Description: Failed to open package file "D:\ssis_packages\CoreLogic\CoreLogicSSIS\Real_Estate_Monthly.dtsx" due to error 0x80070003 "The system cannot find the path specified.". 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: {07BB41B6-D0DF-4349-B9AE-498397D214D7} Started: 1:42:42 AM Finished: 1:42:42 AM Elapsed: 0.047 seconds. The package could not be found. The step failed.

I need some suggestion as what is the best way to run ssis package when sql server and packages are on different machines.

1
sql-serverssissql-server-2016sql-agent-job
There have no issues to execute SQL Job with SSIS on one machine and Targeted SQL Database on different machines! But Let me confirm, are you copying the package for release, from one machine to another ??? – Irfan
The packages stay where they are developed and are picked from filesystem by the agent job. – Ankit Mahajan
What SSDT version been used for SSIS development? – Irfan
I am using ssdt version 15.9.18 on visual studio 2017 – Ankit Mahajan
I agree with the below @user8976869's mentioned answer. Mainly the reason looks like your SQL-Agent user is not having your SSIS package path access!! – Irfan

1 Answers

1
votes

There can be below defined reasons and solutions.

1. The error says it cannot find the package. Just double-check if this is the correct path for your package:

D:\ssis_packages\CoreLogic\CoreLogicSSIS\Real_Estate_Monthly.dtsx

2. It looks like a local path, so if indeed your package is on the D: Drive of another server make sure to add the server name in front of the path:

//server-name/ d$/ ssis_packages\CoreLogic\CoreLogicSSIS\Real_Estate_Monthly.dtsx

3. Grant the account running your SQL Agent job (I am guessing it's the SQL Agent account, but you should double-check that) access rights to the folder where your SSIS package is stored.

4. If the above did not solve the issue, also check if the SSIS package and your SSIS installation on the server where you are running it are the same. Running a 32bit SSIS package on a 64-bit system/SSIS will result in a similar error message.