SSIS deployment

etrochimiuk · 4 August 2020 18:40

Hello,
What I’m trying to do is to deploy ispac SSIS projects to my server on which I have tentacle installed. For that purpose I make a use of “Deploy ISPAC SSIS project from a package” step. I have pushed package with my ispac projects to the server using “Deploy a package” step which I later used in ISPAC SSIS deployment step under “NuGet package step” property. I specified SQL Server I would like to deploy a package to. Initially I would be given an error informing that SQL CLR is not enabled so I enabled it and now step fails with following error:

2 .ispac file(s) found.
21:20:42 Info | Connecting to server …
21:20:42 Info | Getting reference to catalog SSISDB
21:20:43 Warning | SQL CLR is not enabled on MYSQLSERVER. This feature must be enabled for SSIS catalogs.
21:20:43 Info | Enabling SQL CLR …
21:20:43 Info | SQL CLR enabled
21:20:43 Info | Creating SSIS Catalog …
21:20:43 Error | Calamari.exe : InvalidOperation: Exception calling “.ctor” with “3” argument(s): “The property ‘Password’ cannot be null or empty.”

Could you please help me with that? Which Password it means?

jeremy.miller · 4 August 2020 19:53

Hi @etrochimiuk,

Thanks for reaching out.

Are you using SQL Authentication or Integrated for your SQL Connection?

Thanks,
Jeremy

etrochimiuk · 5 August 2020 06:45

Hi @jeremy.miller
In our Microsoft SQL Server Management Studio we have both SQL and Windows authentication modes. Is that what you’re asking about?

My step is configured as follows:

jeremy.miller · 5 August 2020 12:54

Hi @etrochimiuk,

Let me reach out to a colleague with this information as I’m a little out of my depth on this template. I’ll update you as soon as I have information but feel free to reach out in the meantime.

Thanks,
Jeremy

jeremy.miller · 5 August 2020 14:50

Hi @etrochimiuk,

Is SSISDB already configured? If so, please set Enable SQL CLR to False since it’s already enabled.

Please let me know if that helps.

Thanks,
Jeremy

etrochimiuk · 5 August 2020 15:49

I ran deployment process couple of times with SSISDB catalog name and enable sql clr = true set. I disabled sql clr property and I’m getting the same error all over again:

17:45:09 Verbose | Executing Deploy SSIS Kuber project from a package (type Run a Script) on ldf-pen-sql01.nonp.pe.ldf.nchosting.dk
17:45:09 Verbose | Using Calamari 9.0.5
17:45:09 Verbose | Using Calamari.linux-x64 9.0.5
17:45:10 Verbose | Starting C:\Windows\system32\WindowsPowershell\v1.0\PowerShell.exe in working directory ‘C:\Octopus\Work\20200805154509-218127-89’ using ‘Western European (DOS)’ encoding running as ‘NT AUTHORITY\SYSTEM’ with the same environment variables as the launching process
17:45:10 Info | When a script or process writes to error out on Windows 2019 (1809) with .NET 4.7.2 , PowerShell may terminate it immediately without allowing further code execution. This also causes only part of the error output to be shown… Install .NET 4.8 or later to resolve this problem. More information.
17:45:10 Verbose | Octopus Deploy: Calamari version 9.0.5
17:45:10 Verbose | Environment Information:
17:45:10 Verbose | OperatingSystem: Microsoft Windows NT 10.0.17763.0
17:45:10 Verbose | OsBitVersion: x64
17:45:10 Verbose | Is64BitProcess: True
17:45:10 Verbose | CurrentUser: NT AUTHORITY\SYSTEM
17:45:10 Verbose | MachineName: LDF-PEN-SQL01
17:45:10 Verbose | ProcessorCount: 8
17:45:10 Verbose | CurrentDirectory: C:\Octopus\Work\20200805154509-218127-89
17:45:10 Verbose | TempDirectory: C:\Windows\TEMP
17:45:10 Verbose | HostProcess: Calamari (9608)
17:45:11 Verbose | Performing variable substitution on ‘C:\Octopus\Work\20200805154509-218127-89\Script.ps1’
17:45:11 Verbose | Executing ‘C:\Octopus\Work\20200805154509-218127-89\Script.ps1’
17:45:11 Verbose | Setting Proxy Environment Variables
17:45:11 Verbose | Name Value
17:45:11 Verbose | ---- -----
17:45:11 Verbose | PSVersion 5.1.17763.1007
17:45:11 Verbose | PSEdition Desktop
17:45:11 Verbose | PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
17:45:11 Verbose | BuildVersion 10.0.17763.1007
17:45:11 Verbose | CLRVersion 4.0.30319.42000
17:45:11 Verbose | WSManStackVersion 3.0
17:45:11 Verbose | PSRemotingProtocolVersion 2.3
17:45:11 Verbose | SerializationVersion 1.1.0.1
17:45:12 Verbose | PowerShell Environment Information:
17:45:12 Verbose | OperatingSystem: Microsoft Windows NT 10.0.17763.0
17:45:12 Verbose | OsBitVersion: x64
17:45:12 Verbose | Is64BitProcess: True
17:45:12 Verbose | CurrentUser: NT AUTHORITY\SYSTEM
17:45:12 Verbose | MachineName: LDF-PEN-SQL01
17:45:12 Verbose | ProcessorCount: 8
17:45:12 Verbose | CurrentDirectory: C:\Octopus\Work\20200805154509-218127-89
17:45:12 Verbose | CurrentLocation: C:\Octopus\Work\20200805154509-218127-89
17:45:12 Verbose | TempDirectory: C:\Windows\TEMP
17:45:12 Verbose | HostProcess: powershell (13828)
17:45:13 Verbose | TotalPhysicalMemory: 134216652 KB
17:45:13 Verbose | AvailablePhysicalMemory: 125476396 KB
17:45:13 Info | Value is False
17:45:13 Info | Package deployed to C:\Octopus\Applications\Development\SSIS_Kuber\310073_1
17:45:13 Info | 1 .ispac file(s) found.
17:45:13 Info | Connecting to server …
17:45:13 Info | Getting reference to catalog SSISDB
17:45:13 Info | Creating SSIS Catalog …
17:45:13 Error | Calamari.exe : InvalidOperation: Exception calling “.ctor” with “3” argument(s): “The property ‘Password’ cannot be nul
17:45:13 Error | l or empty.”
17:45:13 Error | At C:\Octopus\Work\20200805154509-218127-89\Bootstrap.ps1:38 char:1
17:45:13 Error | + & “${env:TentacleHome}\Calamari\9.0.5\Calamari.exe” run-script -sens …
17:45:13 Error | + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
17:45:13 Error | + CategoryInfo : NotSpecified: (InvalidOperatio…null or empty.":String) [], RemoteException
17:45:13 Error | + FullyQualifiedErrorId : NativeCommandError
17:45:13 Error | At C:\Octopus\Work\20200805154509-218127-89\Script.ps1:42 char:17
17:45:13 Error | + … $Catalog = New-Object “$ISNamespace.Catalog” ($integrationServices, …
17:45:13 Error | + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
17:45:13 Error | at Get-Catalog, C:\Octopus\Work\20200805154509-218127-89\Script.ps1: line 42
17:45:13 Error | at , C:\Octopus\Work\20200805154509-218127-89\Script.ps1: line 359
17:45:13 Error | at , C:\Octopus\Work\20200805154509-218127-89\Bootstrap.Script.ps1: line 2574
17:45:13 Error | at , : line 1
17:45:13 Error | at , : line 1
17:45:15 Verbose | Process C:\Windows\system32\WindowsPowershell\v1.0\PowerShell.exe in C:\Octopus\Work\20200805154509-218127-89 exited with code 1
17:45:15 Verbose | Updating manifest with output variables
17:45:15 Verbose | Updating manifest with action evaluated variables
17:45:15 Fatal | The remote script failed with exit code 1
17:45:15 Fatal | The action Deploy SSIS Kuber project from a package on ldf-pen-sql01.nonp.pe.ldf.nchosting.dk failed

jeremy.miller · 5 August 2020 16:39

Hi @etrochimiuk,

Could you please install .net 4.8 and see if the error changes? Sometimes if we dont have that installed we get a red herring on the error.

Thanks,
Jeremy

jeremy.miller · 5 August 2020 17:10

One more set of questions as well, does SSISDB exist and is that the catalog name?

etrochimiuk · 6 August 2020 08:32

I haven’t configured it myself in the server. Process step doesn’t do it for me?

jeremy.miller · 6 August 2020 14:37

Hi,

It can create one but you have to provide a password if you want it to create it. Can you give that a try?

Thanks,
Jeremy

etrochimiuk · 7 August 2020 10:20

Hi Jeremy,

Thanks for you support. I managed to have it fixed. I didn’t provide a password for SSISDB catalog but specified the name of the catalog thus Octopus couldn’t create a catalog for me. Now it’s working as expected.

Cheers!

jeremy.miller · 7 August 2020 13:08

Hi @etrochimiuk,

You’re very welcome! Thanks for updating me.

I’m glad we got it going. I hope you have a great weekend.

Thanks,
Jeremy

system · 7 September 2020 13:08

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.