May 27, 2014 at 7:16 am
I have the following configuration.
ServerA has an integration package saved with "EncryptSensitiveWithPassword" as protection Level
ServerB has a sqljob that executes package stored on ServerA
The package has a Foreach ADO Enumerator container that sets the value of a variable to a Servername.
This variable is used to create a dynamic connection to other SQL Servers with SQL server authentication.
I created a proxy account on ServerB that is sysadmin and member of db_ssisadmin on ServerA
the job on ServerB executes under configured proxy account
the connection to ServerA is configured with an SQL user with sysadmin privileges on ServerA
The package works using "dtexec /SQL "\PackageFolder\Package" /SERVER ServerA /USER SysadminServerA /Password SQLPassword /DECRYPT PackagePassword /CHECKPOINTING OFF /REPORTING E" from a cmdline
When I run the job I receive an error:
Executed as user: ProxyAccount.
Microsoft (R) SQL Server Execute Package Utility Version 10.50.4276.0 for 64-bit Copyright (C) Microsoft Corporation 2010.
All rights reserved. Started: 2:24:56 PM Error: 2014-05-27 14:24:57.27
Code: 0xC0014023 Source: Foreach Servername
Description: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80070002 "The system cannot find the file specified.".
This occurs when the ForEach Enumerator cannot enumerate. End Error DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:24:56 PM Finished: 2:24:57 PM Elapsed: 1.123 seconds. The package execution failed. The step failed.
Note:
delegation of authority is not enabled.
Is there anyone who can point me in a direction to get the execution of this package from within a SQL server job working?
May 27, 2014 at 12:55 pm
In the SQL Server Agent jobstep you should be able to view the command line used. Check it and see if there any differences with the command line that works.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 28, 2014 at 3:00 am
I found the cause.
On the SQL server where the job was created, IS was not installed.
The dtexec executable is present, but it seems you have to install the complete integration server, executable is not sufficient.
This is a pitty as this server is clustered and installing Integration Services is not an option for us.
I will have to create the Job at another server where Integration services is installed.
May 28, 2014 at 12:46 pm
Strix (5/28/2014)
I found the cause.On the SQL server where the job was created, IS was not installed.
The dtexec executable is present, but it seems you have to install the complete integration server, executable is not sufficient.
This is a pitty as this server is clustered and installing Integration Services is not an option for us.
I will have to create the Job at another server where Integration services is installed.
The DTEXEC executabl is installed for maintenance plan and the import/export wizard, but I think you need additional binaries to run SSIS packages.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply