0

I am receiving an error when I run a SQL Server Agent job that calls an SSIS package that then runs an "Execute Process Task" of a batch file. Specifically the error I get is this

In Executing "d:\apps\bin\DoExport.bat" "-out ACNOR.TXT" at "", The process exit code was "1" while the expected was "0".

I then can see further into the error which says:

%HOMEDRIVE%%HOMEPATH%: No such file or directory at D:/apps/perl/line.pm line 1345. BEGIN failed--compilation aborted at d:/apps/bin/Import/JDBC_DBH.pm line 25. Compilation failed in require at d:/apps/bin/Import/DBWrapper.pm line 280.

I am assuming this is a permissions issue from my SQL Agent because I can run this interactively in BIDS on this server.

The Agent runs with a SQL ID that has sysadmin permission in SQL server and also has admin access on the server that it is running. In the SQL server services (SQL Server, SQL Server Agent, and SQL Server Integration Services) I have an ID setup that has admin access to the server as well.

Any idea what could be causing this issue?

Schwimms
  • 85
  • 1
  • 1
  • 14
  • Is the SQL Server Agent user a domain account or something like "NT Service\MSSQL$SQLAGENT" ? I'm not sure if a reference to %HOMEPATH% works with latter types. You can also try to add your username as a SSIS execution proxy account to SQL Server to make sure it's a credential related problem – Gigga May 14 '20 at 17:05
  • Thanks for the reply. The SQL Server Agent User is a domain account and is also setup as the proxy account for this job. – Schwimms May 14 '20 at 17:23
  • It's a bit long shot, but what about 32bit vs 64bit, what kind of server you have? Have you changed the SSIS project properties to run as 64bit? If not, try to change the job step to run as 32 bit (https://stackoverflow.com/a/28325980/5605866) – Gigga May 14 '20 at 18:08
  • I have tried both and still received the same problem. – Schwimms May 15 '20 at 13:32
  • Interesting problem. What does the bat and the perl-files actually do? Can you show the contents of them? At least from file line.pm, line 1345 and a few lines around it plus the bat file. – Gigga May 16 '20 at 14:03
  • I figured a work around.. posting it now. – Schwimms May 18 '20 at 21:09

1 Answers1

0

The best thing I could do was just hardcode the %HOMEDRIVE%%HOMEPATH% in the first part of the DoExport.bat. Not ideal but better than nothing. Example:

SET HOME=C:\Users\UserX
SET HOMEDRIVE=C:
SET HOMEPATH=C:\Users\UserX
Schwimms
  • 85
  • 1
  • 1
  • 14