Virtual-environments: SSIS Build Sometimes Fails on 'vs2017-win2016' Agent

[PraveenKalavai]

Describe the bug
I have tried building around 16 of our SSIS Solutions using the 'vs2017-win2016' agent in Azure DevOps. 6 of the SSIS Solutions build successfully, but the other 10 SSIS Builds are failing.

The build is triggered on the agent by invoking devenv.
_

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\devenv.com' '$(build.SourcesDirectory)\SolutionName.sln' /Deploy Development /Log '$(Build.SourcesDirectory)activitylog.txt' /Out '$(Build.SourcesDirectory)\buildlog.txt'

_

Area for Triage:
Area: VisualStudio 2017
Area: SSDT 2017
Area: SSIS 2017

Question, Bug, or Feature?:
Bug

Virtual environments affected

• [ ] macOS 10.15
• [ ] Ubuntu 16.04 LTS
• [ ] Ubuntu 18.04 LTS
• [x] Windows Server 2016 R2
• [ ] Windows Server 2019

Expected behavior
SSIS Solution should build successfully and generate .ispac file as the build artifact.

Actual behavior

• Here is a screen shot of a build pipeline.

• This is the error message from the build log

• Here are the error messages from IDE activity log

<entry>
    <record>618</record>
    <time>2020/04/01 21:47:49.902</time>
    <type>Error</type>
    <source>Extension Manager</source>
    <description>**Extension will not be loaded because an extension with the same ID &apos;Microsoft.Windows.DevelopmentKit.Desktop&apos; is already loaded at C:\PROGRAM FILES (X86)\COMMON FILES\MICROSOFT\EXTENSIONMANAGER\EXTENSIONS\MICROSOFT\WINDOWS KITS\10\DESKTOP SDK\...**</description>
    <path>C:\PROGRAM FILES (X86)\COMMON FILES\MICROSOFT\EXTENSIONMANAGER\EXTENSIONS\MICROSOFT\WINDOWS KITS\8.1\DESKTOP SDK\</path>
  </entry>

<entry>
    <record>619</record>
    <time>2020/04/01 21:47:49.902</time>
    <type>Error</type>
    <source>Extension Manager</source>
    <description>**Extension will not be loaded because an extension with the same ID &apos;Microsoft.Windows.DevelopmentKit.WindowsStore&apos; is already loaded at C:\PROGRAM FILES (X86)\COMMON FILES\MICROSOFT\EXTENSIONMANAGER\EXTENSIONS\MICROSOFT\WINDOWS KITS\10\WINDOWS STORE SDK\...**</description>
    <path>C:\PROGRAM FILES (X86)\COMMON FILES\MICROSOFT\EXTENSIONMANAGER\EXTENSIONS\MICROSOFT\WINDOWS KITS\8.1\WINDOWS STORE SDK\</path>
  </entry>
  <entry>

<entry>
    <record>107</record>
    <time>2020/04/01 21:47:47.560</time>
    <type>Error</type>
    <source>VisualStudio</source>
    <description>**SetSite failed for package [Scc Display Information]**Source: &apos;&apos; Description: Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL))&#x000D;&#x000A;System.Runtime.InteropServices.COMException (0x80004005): Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL))&#x000D;&#x000A;   at EnvDTE.Commands.Item(Object index, Int32 ID)&#x000D;&#x000A;   at Microsoft.Internal.VisualStudio.Shell.KeyBindingHelper.&lt;GetKeyBindingsImpl&gt;d__4.MoveNext()&#x000D;&#x000A;   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)&#x000D;&#x000A;   at Microsoft.Internal.VisualStudio.Shell.KeyBindingHelper.GetKeyBinding(Guid commandGroup, Int32 commandId, Guid keyBindingScope)&#x000D;&#x000A;   at Microsoft.Internal.VisualStudio.Shell.KeyBindingHelper.GetGlobalKeyBinding(Guid commandGroup, Int32 commandId)&#x000D;&#x000A;   at Microsoft.VisualStudio.PlatformUI.Packages.Scc.KeyBindingProvider.GetKeyBinding(Guid commandSet, Int32 commandId)&#x000D;&#x000A;   at Microsoft.VisualStudio.Services.SccDisplayInformationService.get_PublishKeyBindingText()&#x000D;&#x000A;   at Microsoft.VisualStudio.Services.SccDisplayInformationService.&lt;InitializeUIAsync&gt;d__71.MoveNext()&#x000D;&#x000A;--- End of stack trace from previous location where exception was thrown ---&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)&#x000D;&#x000A;   at Microsoft.VisualStudio.Services.SccDisplayInformationService.&lt;InitializeAsync&gt;d__17.MoveNext()&#x000D;&#x000A;--- End of stack trace from previous location where exception was thrown ---&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)&#x000D;&#x000A;   at Microsoft.VisualStudio.Services.SccDisplayInformationPackage.&lt;InitializeAsync&gt;d__2.MoveNext()&#x000D;&#x000A;--- End of stack trace from previous location where exception was thrown ---&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)&#x000D;&#x000A;   at Microsoft.VisualStudio.Shell.AsyncPackage.&lt;&gt;c__DisplayClass17_0.&lt;&lt;Microsoft-VisualStudio-Shell-Interop-IAsyncLoadablePackageInitialize-Initialize&gt;b__1&gt;d.MoveNext()&#x000D;&#x000A;--- End of stack trace from previous location where exception was thrown ---&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)&#x000D;&#x000A;   at Microsoft.VisualStudio.Threading.ThreadingTools.&lt;WithCancellationSlow&gt;d__12.MoveNext()&#x000D;&#x000A;--- End of stack trace from previous location where exception was thrown ---&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)&#x000D;&#x000A;   at Microsoft.VisualStudio.Threading.JoinableTask.&lt;JoinAsync&gt;d__78.MoveNext()&#x000D;&#x000A;--- End of stack trace from previous location where exception was thrown ---&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)&#x000D;&#x000A;   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)&#x000D;&#x000A;   at Microsoft.VisualStudio.Threading.JoinableTask.CompleteOnCurrentThread()&#x000D;&#x000A;   at Microsoft.VisualStudio.Threading.JoinableTask.Join(CancellationToken cancellationToken)&#x000D;&#x000A;   at Microsoft.VisualStudio.Threading.JoinableTask`1.Join(CancellationToken cancellationToken)&#x000D;&#x000A;   at Microsoft.VisualStudio.Shell.VsTaskLibraryHelper.&lt;&gt;c__DisplayClass33_1`1.&lt;AsVsTask&gt;b__3()&#x000D;&#x000A;--- End of stack trace from previous location where exception was thrown ---&#x000D;&#x000A;   at Microsoft.VisualStudio.Services.VsTask.RethrowException(AggregateException e)&#x000D;&#x000A;   at Microsoft.VisualStudio.Services.VsTask.InternalGetResult(Boolean ignoreUIThreadCheck)&#x000D;&#x000A;   at Microsoft.VisualStudio.Services.VsTask.GetResult()</description>
    <guid>{D7BB9305-5804-4F92-9CFE-119F4CB0563B}</guid>
    <hr>80004005 - E_FAIL</hr>
  </entry>

[PraveenKalavai]

Upon further analysis of the activity logs between 'failed' and 'succeeded' builds, I noticed that the only difference is the error about package "Scc Display Information". I suspect this is what is causing the SSIS builds to fail.

_Package 'Scc Display Information' failed to load._
------ Build started: Project: SSISProjectName (SQL Server 2017), Configuration: Development ------
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped ==========

The first two errors are present even in the activity logs of successful builds.

_Extension will not be loaded because an extension with the same ID 'Microsoft.Windows.DevelopmentKit.Desktop' is already loaded at C:\PROGRAM FILES (X86)\COMMON FILES\MICROSOFT\EXTENSIONMANAGER\EXTENSIONS\MICROSOFT\WINDOWS KITS10\DESKTOP SDK..._

_Extension will not be loaded because an extension with the same ID 'Microsoft.Windows.DevelopmentKit.WindowsStore' is already loaded at C:\PROGRAM FILES (X86)\COMMON FILES\MICROSOFT\EXTENSIONMANAGER\EXTENSIONS\MICROSOFT\WINDOWS KITS10\WINDOWS STORE SDK..._

[al-cheb]

Hello, @PraveenKalavai
Could you please provide project and minimal steps to reproduce the issue?

[PraveenKalavai]

@al-cheb I can't seem to figure out what is causing certain builds to fail in the Azure Pipeline on the vs2017-win2016 agent.
Our SSIS Solutions are built on VS2017, targeting SQL Server 2017. I cannot share the projects I'm working on since they're client solutions.

I will try to build a dummy ssis project, try to see if I can reproduce the issue and will share it.

[maxim-lobanov]

@PraveenKalavai, any updates?

[PraveenKalavai]

@al-cheb @maxim-lobanov
I tried to compare the build log of a successful build with the build log of a failed build.
The build log always fails after the step 'applied active configuration to 'Project.params'"
The next step in the build should be to apply the active configuration to each package. So the steps to build the packages were failing.

I deleted the first package. Still the same error.
I kept deleting each package (.dtsx) to isolate the error until the build succeeded.
The build was failing even after deleting all the packages. That made me think the error could be due to connection managers or project parameters.

Now I tried to build with just the connection managers. This gave me a different error.

System.ArgumentException: Unable to create the type with the name 'SQLPDW'.

I deleted the SQLPDW Connection Manager and rebuilt. This gave a different error.

System.ArgumentException: Unable to create the type with the name 'ORACLE'.

I then deleted the Attunity connection manager, and the solution successfully built.

Those errors typically come when the additional drivers/adapters are unavailable on the 'vs2017-win2016' agent.

The Oracle error refers to "Attunity Connection manager"
Microsoft Connector Version 5.0 for Oracle by Attunity:
https://www.microsoft.com/en-us/download/details.aspx?id=55179

SQLPDW Error refers to "PDW Destination Adapters"
Analytics Platform System Appliance Update 5 Documentation and Client Tools:
https://www.microsoft.com/en-us/download/details.aspx?id=51610

Can you please check once and let me know if Attunity Drivers and PDW Destination Adapters are installed on the Agent?

[al-cheb]

Hello, @PraveenKalavai
I have checked and none of these adapters present on our images(Microsoft Connector Version 5.0 for Oracle by Attunity/ PDW Destination Adapters).

[PraveenKalavai]

@al-cheb
Thanks for the confirmation.
What is the process to get these adapters installed to the agents?

[al-cheb]

@PraveenKalavai,

https://social.msdn.microsoft.com/Forums/en-US/d0d45473-eea6-4d19-a708-25ceb79bf920/ssdt-for-visual-studio-2017-1590-and-oracle-connector-requirements?forum=ssdt

SSDT for Visual Studio 2017 (15.8.0 and higher) doesn't support designing packages that contain Oracle/Teradata source/destination. Use SSDT for Visual Studio 2017 (15.8).

1. Microsoft Connector Version 5.0 for Oracle by Attunity required installed MS SQL Server 2017. We don't have any plans to install MS SQL 2017 server on an image.

2. https://docs.microsoft.com/en-us/sql/ssdt/release-notes-ssdt?view=sql-server-2017#1593-ssdt-for-vs-2017

Removed the inbox component Microsoft Oracle Connector for SQL Server 2019. Now we have announced Microsoft Oracle Connector for SQL Server 2019 as out-of-box component, which can be downloaded here.

To install Microsoft Oracle Connector for SQL Server 2019/PDW Destination Adapters at runtime:

- name: Adapters
  run: |
        $oracleUrl = "https://download.microsoft.com/download/9/F/C/9FCD89BF-D8B0-4FBF-98C0-7B873F9CA9B0/MicrosoftSSISOracleConnectorX86.msi"
        $pwdUrl = "https://download.microsoft.com/download/9/9/C/99C59D52-615C-4ED4-B4D1-36A062CABD44/sql2016-ssis-pdw-destination-adapter-x86-pdwau5.msi"

        $oracleAdapters = "$env:Temp\MicrosoftSSISOracleConnectorX86.msi"
        $pwdAdapters = "$env:Temp\sql2016-ssis-pdw-destination-adapter-x86-pdwau5.msi"

        Invoke-WebRequest $oracleUrl -OutFile $oracleAdapters
        Invoke-WebRequest $pwdUrl -OutFile $pwdAdapters


        $out = $oracleAdapters, $pwdAdapters | Foreach {
            $ArgumentList = ('/i', $_, '/QN', '/norestart')
            Start-Process -FilePath msiexec.exe -ArgumentList $ArgumentList -Wait -PassThru
        }

[PraveenKalavai]

@al-cheb
Thank you.
If I run this piece of code as an inline script in PowerShell task in the build pipeline using the 'vs2017-win2016' agent, would it install the drivers during runtime?

[al-cheb]

@PraveenKalavai, It should work at runtime .

[PraveenKalavai]

@al-cheb
The runtime installation of Attunity drivers seems to be working. I'm not getting any build error with that.
However, the SQLPDW component is still failing with the error
System.ArgumentException: Unable to create the type with the name 'SQLPDW'.

I checked with our DBA and found out that our PDW Appliance version was recently upgraded to AU7.
Here is the download link for AU7 PDW destination adapters.
https://www.microsoft.com/en-us/download/confirmation.aspx?id=57472

Let me try to find the .msi download urls for the highlighted 32 bit and 64 bit drivers.
I will plug them in your script, install them during runtime and try building my SSIS Solution again.
Hopefully it would work this time.
I apologize for the confusion.

[PraveenKalavai]

@al-cheb
After updating the script with AU7 PDW destination adapters and installing them during runtime, I'm now able to build my SSIS solutions successfully!

Thanks for the help with the resolution.
This issue can now be closed.