Issues with Template - Deploy ISPAC SSIS project from a package

Legacy Execution
#1

Hello ,
I am facing several issues when using the mentioned Octopus template for deploying an SSIS package (with project deployment mode). The main issues are I think,

  1. When the underlying powershell script tries to create an instance of object “Microsoft.SqlServer.IntegrationServices.IntegrationServices” I get the following exception. I am assuming its a permission issue of the account under which Octopus is running the script. But unable to figure out the steps needed to fix this issue.

15:41:56 Error | New-Object : Exception calling “.ctor” with “1” argument(s): "Failed to
15:41:56 Error | connect to server .“
15:41:56 Error | At C:\Octopus\Work\20170804204135-1106-47\Script.ps1:11 char:24
15:41:56 Error | + $IntegrationServices = New-Object $SsisNamespace”.IntegrationServices"
15:41:56 Error | $SqlConne …
15:41:56 Error | +
15:41:56 Error | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
15:41:56 Error | + CategoryInfo : InvalidOperation: (:slight_smile: [New-Object], MethodInvoca
15:41:56 Error | tionException
15:41:56 Error | + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.Power
15:41:56 Error | Shell.Commands.NewObjectCommand

  1. SECOND ISSUE:
    I have created a Nuget package but not sure what path needs to specified in the field “Ispac File Path field”. As an ugly work around, we are trying to use a zip file instead of a nuget for packging. And added an extra step of unzipping and specifying the path of .ispac file.

Any help in this regard would be greatly appreciated.

Regards,
Shashanka S

#2

Hi Shashanka,

  1. The account your Octopus service is running under needs permission to your database as the connection is using integrated security. This page has some good instructions on how to do this)

  2. Which step from the community library are you using? The Deploy ISPAC SSIS project from a package doesn’t have a parameter that matches Ispac File Path, this step on the other hand does.

The path should be something similar to $OctopusParameters["Octopus.Action[NameOfYourNuGetPackageStep].Output.Package.InstallationDirectoryPath"].

I hope that helps!

Thank you and best regards,
Henrik

#3

Hello Henrik,

Thanks for your email. Actually I got it worked by extracting Power shell script out and pass parameters as you specified by Octopus.Action……
Really liked that feature.

But when using the template I am getting some errors as following line is not getting evaluated.

$OctopusParameters[“Octopus.Action[NameOfYourNuGetPackageStep].Output.Package.InstallationDirectoryPath”].

Will try now…hoping will find some solution. Any suggestion would be appreciated.

Thank you

#4

Hi Shashanka,

Did you replace NameOfYourNuGetPackageStep in $OctopusParameters["Octopus.Action[NameOfYourNuGetPackageStep].Output.Package.InstallationDirectoryPath"] with the actual name of the step that handles the transfer and extraction of your NuGet package?

Thanks,
Henrik

#5

Hello Henrik,
Actually I have got it worked but by putting the following expression in the “Ispac File Path” field

#{Octopus.Action[DeploySSISPackage].Output.Package.InstallationDirectoryPath}$IspacFileName

So looks like we need to add action in #{}

Thank you

#6

Hi Shashanka,

Great to hear that you got it working!

Thanks,
Henrik

#8