SSIS Package works under Catalog but fails under SQL Agent Account – ZappySys Help Center

Problem:

When you run a package from SSIS Catalog (Right click in SSMS > SSISDB > and Execute Package File) it works fine but when you run under SQL Agent Job (Use Proxy or Default account) it may fail with the following error. This error possibly occurs in a cluster environment (Active-Active or Active-Passive Node) or non-cluster environment using DOMAIN user for Agent Service Account.

System tried to search for the following license types [SSIS_PP_ENT, SSIS_PP_PRO, …….., SSIS_PP_TRIAL] under the following path [C:\ProgramData\ZappySys\SSISPowerPack] but no valid license found. Additional information >>> LoggedInUser=MYDOMAIN\MYSERVICEACCOUNT >> RunAsUser=MYDOMAIN\MYSERVICEACCOUNT >>> Path access=True >> >> TempFolder=C:\Windows\TEMP\ [Read/Write=OK,OK] >> License Files Scanned [2] ==> C:\ProgramData\ZappySys\SSISPowerPack\SSIS_PP_TRIAL.lic.130498499674709531 [Read=OK], . Please read this link for more information => https://zappysys.com/links/?id=10021. Inner error=> License type [SSIS_PP_TRIAL] not found or its expired. You are running TRIAL build, if you have paid license then you must download full version

Possible Cause:

Your SQL Agent account or Proxy Account under which Package is executing may not have the correct permissions to access C:\ProgramData\ZappySys folder or it may not have the correct permissions to access Temp directory.

Possible Solution:

First, you should try basic troubleshooting using steps listed here. If your error still didn't go away then continue with following troubleshooting. Some customer tried following steps to fix permission chain issue and it worked for them.

Step:1 - Create a sample SSIS Package (zappy.dtsx) for testing and copy to server

To minimize troubleshooting of unwanted errors we will first create a very simple SSIS Package which uses ZappySys Component (e.g. JSON Source).

Create a new SSIS Project
Add new SSIS Package (lets rename it to zappy.dtsx)
Drag and drop data flow task and double click it
Drag and drop ZS JSON Source and double click to edit it
Click on Example1 link on JSON Source UI to use direct JSON mode (This way no internet needed)
Click OK to save with default settings
Drag and drop ZS Trash Destination
Connect ZS JSON Source to Trash Destination
Save Package and copy Package to server (via RDP or via Shared location). Make sure you copy this package to such location which can be accessed by SQL Agent Account (Dont copy to Desktop if you are using your own RDP session because service account can't read your Desktop folder).

Step:2 - Run SSIS Package using DTEXEC (File System)

Rename old ZappySys folder found under C:\ProgramData\
Now launch command line with "Run As" option (Hold Shift Key and Right click on Command Line ShortCut)
Enter your SQL Agent Service Account (or Proxy Account User) credentials.
Now you can execute following command (Assuming you copied SSIS package to c:\temp folder

dtexec /F c:\temp\zappy.dtsx
Validate Success at the end

Step:3 - Run SSIS Package under SQL Agent Job (File System Mode)

If above step works fine (i.e. if you get DTSER_SUCCESS), Now lets test same SSIS Package under SQL Agent Job

Create new SQL Agent Job > New Job Step (Type = SQL Integration Services)
Select SSIS Package Source = File System
Enter or browse file path
Now execute the Job and confirm Success

Step:4 - Deploy SSIS Package to Catalog

If previous step is green means package is working fine when you use file system package (Avoid SSIS Catalog hence avoid SSIS Service Account).

Step:5 - Run SSIS Package under SQL Agent Job (SSIS Catalog Mode)

If above step works fine means you fixed the permission chain issue. When you executed package using DTEXEC it may fix permission chain issue and there after JOB may execute fine for existing packages.

View SSIS Package Execution