0

I've been tasked with deploying a handful of SSIS packages on Microsoft SQL Server 2012 Enterprise 64-bit (11.0.6020.0) running on Windows Server 2012 R2.

The packages run perfectly when run from SQL Management Studio by right-clicking the package and clicking execute with the proper parameters.

However, when I schedule them to run under the SQL Server Agent, some of them fail. I have not been able to identify any pattern that distinguishes the failing packages from the working packages.

All of the packages are run using a, probably unconventional, approach which involves creating the job step in the job as a Operating system (CmdExec) step rather than a SQL Server Integration Services Package step. This is because the packages depend on access to a particular SMB share and must have credentials provided first to access this share. The CmdExec step calls a wrapper batch file and passes the package name and the environment reference for dtexec to run the package.

Here's the file bat file:

set ETL_PACKAGE=%1
set ETL_ENV_REF=%2

net use \\remote-share.some-domain.local\dropfolder\in /user:ssisagent mypassword

dtexec /Envreference %ETL_ENV_REF% /ISSERVER \SSISDB\%ETL_PACKAGE%

if "%errorlevel%"=="1" (
exit /b %errorlevel%
)

When the agent runs these jobs by rightclicking the job and selecting "Start Job at Step..." the job is started succesfully, but when you pull an execution report it fails with this error:

Message Source Name: Transact-SQL stored procedure

Operation is not valid due to the current state of the object.;
at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ISServerExecutionEvents.LoadPackage(PackageItem item)
at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ProjectOperator.StartPackage()
at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ProjectOperator.PerformOperation()

enter image description here

I would appreciate ANY idea about what could possibly cause this error or how I can troubleshoot it to narrow down the causes.

I have tried setting up a proxy account using my own admininstrator credentials to rule out possible permission issues, but it doesn't change a thing. I have checked permissions on the SSIS environment used, but that doesn't change anything.

The packages in question interact with DB2, MS SQL and various fileshares with CSV/XML files.

Niels B.
  • 5,912
  • 3
  • 24
  • 44
  • >>>I have not been able to identify any pattern that distinguishes the failing packages from the working packages<<< Did you check job **owner** for these jobs? – sepupic Oct 31 '17 at 09:16
  • No, I didn't... only the account **running** the job ... what influence does the job owner have and what should it match? – Niels B. Oct 31 '17 at 09:22
  • If you use job step is **T-SQL step**, when the job owner is sysadmin the step is executed under SQL Server service account/agent account when scheduled, if it's not a sysadmin, the step is executed under job owner's accout – sepupic Oct 31 '17 at 09:33
  • I see, but the peculiar thing is that these jobs are not T-SQL steps. They all consist of one CmdExec step. – Niels B. Oct 31 '17 at 09:38
  • Sorry, I thought you used t-sql step instead of ssis step – sepupic Oct 31 '17 at 09:48

0 Answers0