SSIS Scripting Component step fails while running an SSIS package using SQL Agent with proxy account.

Question

Hello together,

we currently face an issue with MS SQL Server Integration Services (SSIS) regarding the use of C# code within Scripting Components.

During the execution of our SSIS packages the following error(s) are thrown by SSIS:

• The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully. • Failed to compiled scripts contained in the package. Open the package in SSIS Designer and resolve the compilation errors. • Error: CS2001 - Source file 'C:\Windows\TEMP.NETFramework,Version=v4.7.AssemblyAttributes.cs' could not be found, CSC, 0, 0

Those errors appear during execution of the SSIS packages using an SQL Server Agent Job. In the past those Agent Jobs where executed under the Account used to run the SQL Server Agent Service. Using those settings the packages worked as expected, executing without trowing any errors. Following a new security requirement of our company we had to switch to an execution under a separate Account. We have created the required Proxy entry under SQL Server Agent -> Proxies -> SSIS Package Execution This proxy is linked to the corresponding AD Account and has the "SQL Server Integration Services Package" subsystem configured as active.

The proxy is then selected in the "Run As" settings used within the SQL Server Agent Job Steps tasked with running the SSIS packages.

SSIS execution starts as expected whe starting the Agent Jobs. However, once a package containing a Script Task reaches the Script Task it will fail and throw the errors mentioned above. However, the errors thrown at us give us no real clue as to where to investigate next.

The Account used by the proxy has been granted the following permissions

• Integration Services Catalogs: -> SSISDB Subfolders: Read + Read Objects + Execute Objects -> SSIS Projects: Read + Execute
• SSISDB Database
  • ssis_admin role
• Databases used by the SSIS Packages
  • db_owner

Some details about the used software • Version MS SQL Server 2019 components (Database, Integration Services, etc.) Vers. 15.0.4073.23 (Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)) • Dev Environment: Visual Studio Professional 2017 Version 15.9.16

We suspect that this is some kind of permission issue ( AD Account missing some permission to read/write from/to disk or missing permissions to compile the script, etc. )

Any tips on where / what to investigate next would be highly appreciated.

Kind regards, Christian Theisen

Answer 1

Hello again,

I just wanted to report back that we managed to find a solution to this specific case.
It turned out that the AD Account used in the proxy settings was simply missing the read/write permissions to the folder "C:\Windows\Temp" where the compiled code would be placed.
After granting the required permissions all the packages can now be run without throwing errors.

King regards,
Christian

Answer 2

Hi @Theisen, Christian ,

Manually delete all the files possible in C:\Users\XXX\AppData\Local\Temp.

And then open the script editor to see if it complains any error in your code.

After that, build it and save your script. Remember to click ok in the script editor.

Redeploy the project to SSMS and run in the agent job for a try.

You may also copy the script to a new SSIS project, re-build and re-deploy for a try.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 3

Hi Zoe,

first please let me thank you for your feedback.

We tried the steps suggested by you on one of our packages but the result (errors) stays the same.
During design time the script does not show any errors. Even a Debug-Testrun of the corresponding package from within Visual Studio finishes without errors. Note that this debug run will be executed with the credentials of the Developer running the Visual Studio instance.
If we deploy the package and run it in an SQL Agent job using the SQL Agent Service account it will still work.
Only after changing the "run as" setting within the SQL Agent Job from the Agent Service account to the proxy account we can observe the errors.

To make this point clear:

1. If we run any of the failing packages from within the SQL Agent Job using the AD Account that the SQL Agent itself is running under, all those scripts will compile without error and the packages will run.
2. Once we assign the proxy account under the "run as" settings within the SQL Agent Job the afromentioned errors occur.
3. If we then switch back to the original AD Account to run the packages then they will once again complete their execution without raising any errors.

Rebuilding the scripts within the script component step does not change this behaviour.
On a side note, we are talking about around 100 packages that use one or more of those script components and i really do not want to believe that "rebuilding the scripts and redeploy" should be the go-to solution to such an issue.

If it is, then we'll have to go this route but as the test suggested by you shows this seems not to be our way forward.

Kind regards,
Christian