Azure Feature Pack on Azure DevOps Hosted Agent?

Question

I have an SSIS project that uses the Azure Storage Connection Manager from SSIS Azure Feature Pack. When this connection is included in the project, the Azure Pipeline Build fails with the following message with a vs2017 hosted agent:

System.ArgumentException: Value does not fall within the expected range. at Microsoft.SqlServer.Dts.Runtime.Interop.ProjectInterop.ReferencePackage(Package package, String packageLocation) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package() at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)

When the Azure Storage Connection Manager is removed the the project, the Azure Pipeline Build is successful.

I have also tried 2019 hosted agent but it failed wit ha different error (Some errors occurred during migration. For more information, see the migration report).

Is the Azure Feature Pack for SSIS not installed on the hosted agents? I would like to resolve this without having to use self hosted or docker.

Answer 1

The capabilities of Microsoft-Hosted agents can be checked here:

https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/hosted?view=azure-devops

They don't have seem to include SSIS support. You can try to install those as part of the build definition. For example:

https://erwindekreuk.com/2019/02/azure-devops-and-azure-feature-pack-for-integration-services/

Answer 2

Thank you JukkaK. The solution you posted worked for me, with a slight change - i had to change all of the single quotes to double quotes - it was not recognizing the variable for the file correctly. Below is the final solution in my implementation:

Write-Information "Installing SSIS Azure Feature Pack 2017"

#Define Filename
$Filename = "SsisAzureFeaturePack_2017_x64.msi"
$Arguments="/qn"
Write-Host "Downloading " $Filename
#Define download link including filename and output directory with filename
Invoke-WebRequest -Uri "https://download.microsoft.com/download/E/E/0/EE0CB6A0-4105- 
466D-A7CA-5E39FA9AB128/SsisAzureFeaturePack_2017_x64.msi" -OutFile 
"$(Build.StagingDirectory)\$Filename"

Write-Host "Installing "$Filename
Invoke-Expression -Command "$(Build.StagingDirectory)\$Filename $Arguments"
Write-Host "Finished Installing " $Filename