SSIS Package error when running through SQL Agent job
-
Hi,
Can someone please help me resolve below error.
To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher. End Error DTExec: The package execution returned DTSER_FAILURE (1).
I'm running my package using SQL Server agent Job. But it is throwing error as above. It is running fine when run on VS 2019.
SQL Server 2019 is installed. SSIS was installed through VS using manage Extensions.
On the same server there are other jobs as well that is running fine while executing other SSIS packages.
Please help
Regards,
Anshu
-
What version of SQL Server 2019 is installed? (Enterprise/Standard/Express etc)
Can you confirm that you have other SQL Agent jobs configured which execute SSIS packages?
The VS extension which allows you to create or modify SSIS projects does not 'install SSIS'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Also SQL Agent job is configured correctly. Other packages are running fine without error.
-
-
That does not tell you the version of SQL Server. Please run this query on the server and post back the results:
SELECT
Version = SERVERPROPERTY('productversion')
, Level = SERVERPROPERTY('productlevel')
, Edition = SERVERPROPERTY('edition');The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
OK, I looked closer at the error and it sounds like Integration Services was not installed on the server. This is part of the SQL Server installation process and not something you run from a client machine.
See the following link for details: https://learn.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-ver16
However, that does not explain why other packages can be run from SQL Agent. That is a puzzle.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
-
If this is a production server, you shouldn't be running Developer Edition on it!
But if it's not, and Integration Services has been installed as per the link I sent earlier, I see no reason why you would receive that message, for just one of your packages. Doesn't make sense, there must be something different about this package, or the way it is configured.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Apart from the server installation, you need to set the correct server version in the project properties of your SSIS project in Visual Studio before deploying the project. Right click the project in the solution explorer window, select properties and then select Configuration properties, General and set the correct Target server version. Then deploy.
Using the configuration manager for the solution, you can have multiple configs to deploy to different servers.
-
this also looks like a possible execution of DTEXEC from the command line (instead of SSIS catalog/SSIS Execute step from sql agent) - and in this case its likely it is using a dtexec that is not of the installed SQL Server instance, but from another folder.
-
I'm trying to run through SQL Agent job. Only issue is coming in the packages where transformations like Multicast, conditional Spilt etc. are being used.
When run directly through Visual studio they run fine.
- anshu.shikha wrote:
I'm trying to run through SQL Agent job. Only issue is coming in the packages where transformations like Multicast, conditional Spilt etc. are being used.
When run directly through Visual studio they run fine.
As you aren't prepared to respond to P Jones or Frederico's posts, my suggestion is that you continue using VS.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply