Azure-pipelines-tasks: SqlDacpacDeploymentOnMachineGroupV0 - environment variable size failure

[amccool]

Related to: https://github.com/microsoft/azure-pipelines-extensions/issues/709
PR: https://github.com/microsoft/azure-pipelines-extensions/pull/710

SqlDacpacDeploymentOnMachineGroupV0 - 0.3.20
is using an older version of vstasksdk (0.8.2)
resulting in System.InvalidOperationException: The environment block used to start a process cannot be longer than 65535 bytes. Your environment block is 69981 bytes long. Remove some environment variables and try again. when using a large number (50) of builds

If I replace
ps_modules\VsTaskSdk(0.8.2) with the ps_modules\VsTaskSdk (0.11.0) from _tasks\Powershell(2.151.2)

and remove Add-Type from TaskModuleSqlUtility.psm1
(https://github.com/microsoft/azure-pipelines-extensions/blob/master/TaskModules/powershell/TaskModuleSqlUtility/TaskModuleSqlUtility.psm1)

the error goes way

Type: Bug

Enter Task Name: https://github.com/microsoft/azure-pipelines-tasks/tree/master/Tasks/SqlDacpacDeploymentOnMachineGroupV0

Environment

• Server - Azure Pipelines

https://dev.azure.com/raulandborg/Enterprise
releaseId=233

• Agent - Private:

agent.version:2.158.0
agent.os:Windows_NT
agent.osarchitecture:x64
agent.osversion:6.3.9600

Issue Description

[error]The environment block used to start a process cannot be longer than 65535 bytes. Your environment block is 69981 bytes long. Remove some environment variables and try again.

Task logs

2019-10-22T22:01:12.4806611Z ##[debug]Loading module from path 'C:\azagent\A1\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.3.20\ps_modules\TaskModuleSqlUtility\TaskModuleSqlUtility.psd1'.
2019-10-22T22:01:12.4924371Z ##[debug]Loading module from path 'C:\azagent\A1\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.3.20\ps_modules\TaskModuleSqlUtility\TaskModuleSqlUtility.psm1'.
2019-10-22T22:01:12.5302331Z ##[debug]NonInteractive: False
2019-10-22T22:01:12.6247888Z ##[debug]Exporting function 'Invoke-DacpacDeployment'.
2019-10-22T22:01:12.6297345Z ##[debug]Exporting function 'Invoke-SqlQueryDeployment'.
2019-10-22T22:01:12.6354879Z ##[debug]Adding exceptions types.
2019-10-22T22:01:12.8405840Z ##[debug]Caught exception from task script.
2019-10-22T22:01:12.8460442Z ##[debug]Error record:
2019-10-22T22:01:13.0209589Z ##[debug]Add-Type : The environment block used to start a process cannot be longer than 65535 bytes.  Your environment block is 69981 bytes long.  Remove some environment variables and try again.
2019-10-22T22:01:13.0253086Z ##[debug]At C:\azagent\A1\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.3.20\ps_modules\TaskModuleSqlUtility\TaskModuleSqlUtility.psm1:28 char:1
2019-10-22T22:01:13.0291491Z ##[debug]+ Add-Type -WarningAction SilentlyContinue -Debug:$false -TypeDefinitio ...
2019-10-22T22:01:13.0329949Z ##[debug]+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2019-10-22T22:01:13.0367923Z ##[debug]    + CategoryInfo          : NotSpecified: (:) [Add-Type], InvalidOperationEx    ception
2019-10-22T22:01:13.0412881Z ##[debug]    + FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.Power    Shell.Commands.AddTypeCommand
2019-10-22T22:01:13.0451396Z ##[debug] 
2019-10-22T22:01:13.0518406Z ##[debug]Script stack trace:
2019-10-22T22:01:13.0633754Z ##[debug]at <ScriptBlock>, C:\azagent\A1\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.3.20\ps_modules\TaskModuleSqlUtility\TaskModuleSqlUtility.psm1: line 28
2019-10-22T22:01:13.0671795Z ##[debug]at <ScriptBlock>, C:\azagent\A1\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.3.20\Main.ps1: line 83
2019-10-22T22:01:13.0714939Z ##[debug]at <ScriptBlock>, <No file>: line 1
2019-10-22T22:01:13.0754792Z ##[debug]at <ScriptBlock>, <No file>: line 22
2019-10-22T22:01:13.0793196Z ##[debug]at <ScriptBlock>, <No file>: line 18
2019-10-22T22:01:13.0833300Z ##[debug]at <ScriptBlock>, <No file>: line 1
2019-10-22T22:01:13.0903951Z ##[debug]Exception:
2019-10-22T22:01:13.1035571Z ##[debug]System.InvalidOperationException: The environment block used to start a process cannot be longer than 65535 bytes.  Your environment block is 69981 bytes long.  Remove some environment variables and try again.
2019-10-22T22:01:13.1075452Z ##[debug]   at System.Diagnostics.EnvironmentBlock.ToByteArray(StringDictionary sd, Boolean unicode)
2019-10-22T22:01:13.1116420Z ##[debug]   at System.CodeDom.Compiler.Executor.ExecWaitWithCaptureUnimpersonated(SafeUserTokenHandle userToken, String cmd, String currentDir, TempFileCollection tempFiles, String& outputName, String& errorName, String trueCmdLine)
2019-10-22T22:01:13.1158493Z ##[debug]   at System.CodeDom.Compiler.Executor.ExecWaitWithCapture(SafeUserTokenHandle userToken, String cmd, String currentDir, TempFileCollection tempFiles, String& outputName, String& errorName, String trueCmdLine)
2019-10-22T22:01:13.1200267Z ##[debug]   at Microsoft.CSharp.CSharpCodeGenerator.Compile(CompilerParameters options, String compilerDirectory, String compilerExe, String arguments, String& outputFile, Int32& nativeReturnValue, String trueArgs)
2019-10-22T22:01:13.1240437Z ##[debug]   at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
2019-10-22T22:01:13.1278725Z ##[debug]   at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
2019-10-22T22:01:13.1321693Z ##[debug]   at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
2019-10-22T22:01:13.1364265Z ##[debug]   at Microsoft.PowerShell.Commands.AddTypeCommand.CompileAssemblyFromSource(List`1 generatedTypes)
2019-10-22T22:01:13.1405191Z ##[debug]   at Microsoft.PowerShell.Commands.AddTypeCommand.EndProcessing()
2019-10-22T22:01:13.1448472Z ##[debug]   at System.Management.Automation.CommandProcessorBase.Complete()

[amccool]

see also
https://github.com/microsoft/azure-pipelines-tasks/issues/7824
and (which should not be closed)
https://github.com/microsoft/azure-pipelines-task-lib/issues/228

[amccool]

https://github.com/microsoft/azure-pipelines-extensions/blob/master/TaskModules/powershell/TaskModuleSqlUtility/TaskModuleSqlUtility.psm1

also requires a change to remove the Add-Type which throws the error

[Ajay-MS]

@amccool

Thanks for raising the PR, we are looking into your PR.

[Ajay-MS]

@amccool

Just to understand the issue more deeply, is large size of the environment is because of the number of builds only or because of other environment variables too.

Also, share your email id so that we can also try out the task with newer version of the Vsts SDK if that helps.

[amccool]

the large environment is due to the number of build artifacts (50+). Where can I private message you for my email address?

[eaarora-ms]

@amccool You can mail us the logs/personal email id om RM_Customer_Queries at microsoft dot com

[arjgupta]

@amccool , did you share the logs ?

[amccool]

tasklog_75.log file was emailed as an attachment Dec 2, 2019 at 10:42 PM PST

[Roshan-Kumar-Microsoft]

@amccool Are you still facing this issue ?

[amccool]

yes on newly deployed build agents. If I manually alter the files on an agent (per the PR) the problem goes away.

[20shivangi]

@amccool Is this issue still occuring ?

[FlorisDevreese]

@20shivangi I'm still having this issue.

Current agent version: 2.144.2

Error log:

##[warning]Environment variable 'VSTS_PUBLIC_VARIABLES' exceeds the maximum supported length. Environment variable length: 41146 , Maximum supported length: 32766
Add-Type : The environment block used to start a process cannot be longer than 65535 bytes.  Your environment block is 
104424 bytes long.  Remove some environment variables and try again.
At D:\TfsBuilds\_work\_tasks\RedgateSqlCloneClone_42eccb69-7062-4d1a-bbd0-251884f2c0de\4.0.0\ps_modules\VstsTaskSdk\Lon
gPathFunctions.ps1:212 char:1
+ Add-Type -Debug:$false -TypeDefinition @'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-Type], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.PowerShell.Commands.AddTypeCommand

Add-Type : The environment block used to start a process cannot be longer than 65535 bytes.  Your environment block is 
104424 bytes long.  Remove some environment variables and try again.
At D:\TfsBuilds\_work\_tasks\RedgateSqlCloneClone_42eccb69-7062-4d1a-bbd0-251884f2c0de\4.0.0\ps_modules\VstsTaskSdk\Vst
sTaskSdk.psm1:80 char:1
+ Add-Type -WarningAction SilentlyContinue -Debug:$false -TypeDefinition @'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-Type], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.PowerShell.Commands.AddTypeCommand

As I understand from this post the issue is in the VstsTaskSdk library.

So I think for me It think this means that I need to update the RedgateSqlCloneClone task I use in the hope that the update contains the new version of VstsTaskSdk that contains the fix for this problem.

[20shivangi]

@FlorisDevreese So have you tried to update the RedgateSqlCloneClone task so that it contains updated VstsTaskSdk ?

[FlorisDevreese]

@20shivangi I just analysed the new version of the RedgateSqlCloneClone extension. It still uses v0.8.1 of the VstsTaskSdk library. That version does not contain the fix.

So an update here won't help...

I'm looking into other option like creating an own version of that extension that contains a new version of the VstsTaskSdk library.

All other suggestions are welcome

[20shivangi]

@FlorisDevreese Thank you for the update. Do let us know when you are able to use the required version of VstsTaskSdk lib

[FlorisDevreese]

@20shivangi I've created a request with the owner of the extension (Red Gate). They forwarded it to there developers.

I will keep you posted on progress (if it doesn't take too long to complete 😉)

[20shivangi]

@FlorisDevreese Are you unblocked now ?

[FlorisDevreese]

@20shivangi Yes, we are unblocked :-)

[20shivangi]

Great, closing it now :)