Octopus Deploy of SSIS reports

ncase · 14 May 2020 13:52

Hello,

I am getting an error message when attempting to use the new community template for deploying ssis reports. I have the deployment logs. Any advise would be much appreciated. ServerTasks-112955.log.txt (10.4 KB)

thank you,
Nate Case

jeremy.miller · 14 May 2020 14:40

Hi Nate,

Sorry to hear about your issues. I took a look at the log and it looks to be a failure to connect to the server. “Failed to connect to server 7CSM-DBS01-DC1.”" The work is being done on worker 1ods-dbs01-dc1. Could you please verify that worker is able to get to that server successfully?

Please let me know what you find out and we’ll go from there.

Thanks,
Jeremy

ncase · 14 May 2020 15:02

After further investigation the server is able to connect to the node, this was after updating the service account used by the tentacle. I have attached the logs the deployment is creating now. ServerTasks-114747.log.txt (15.6 KB)

jeremy.miller · 14 May 2020 17:34

Hi Nate,

Now we’re getting an access denied on folder D:\Octopus\Files\CaseSmart_FTA_Payment_SSIS@S1.44@044FCC2BAEEACC4DB857438AD49338B6.nupkg

Does the account that’s running the database engine have access to that directory?

Thanks,
Jeremy

ncase · 15 May 2020 20:40

After double checking the permissions of that directory the account running sql server does have access to that directory. I am still getting the error attached. ServerTasks-115089.log.txt (10.9 KB)

jeremy.miller · 15 May 2020 21:05

Hi Nate,

Could you please check which version of the community step you’re running? Have you guys made any changes to the script at all? Would it be possible for you to copy the community step script and attach it here? It might also be helpful for us to troubleshoot this to add a bunch of write-hosts to figure out where the SQLSERVER drive error pops out as well as the Access Denied error. Let me know what you think.

Thanks,
Jeremy

ncase · 18 May 2020 15:15

I have attached the deployment scripts per your request. It is up to date according to Octopus Deploy. We have not made any modifications to the script. I also attached the lastest failed deployment logs for your convenience.

Communityscript.ps1 (18.7 KB)
ServerTasks-115089.log.txt (10.9 KB)

jeremy.miller · 18 May 2020 18:08

Hey Nate,

We took a look at the logs and the only thing that jumps out as us is the line 13:05:50 Error | A drive with the name 'SQLSERVER' already exists. We do not know where this error is coming from.

We have run the same script locally and have no errors. Since we cant reproduce the issue (or error) locally, would you be willing to edit the script and add in some Write-Hosts for debugging to see where the issue itself and the SQLSERVER error kick in? If we could narrow it down to the specific line they occur on we may get a better idea of what’s happening.

Let me know if that’s something you can do.

Thanks,
Jeremy

ncase · 19 May 2020 06:03

The script has a number of Write-Host included already, can you please be more specific as to what portion of the script you want debug output from?
thank you,
Nate

jeremy.miller · 19 May 2020 15:41

Hey Nate,

Can you please add Write-Host "Including file $($assemblyFile.FullName)" after lines 55, 63, and 69? Once you’ve done that, please run it again and upload the new log so we can take a look.

Thanks,
Jeremy

Shawn_Sesna · 22 May 2020 16:47

Greetings! Just a note of clarification, the “Including file $($assemblyFile.FullName)” is only for line 69, lines 55 and 63 can be a message indicating that’s where we are.

ncase · 26 May 2020 17:07

I have added the Write-Host per your request and included the logs. Please let me know if you need further information.
Octopus Server version: 2020.1.14+Branch.tags-2020.1.14.Sha.fb3f216fabef1e7ce3aedf079621eb061ee254dc
May 26th 2020 11:38:27Verbose
Environment Information:
OperatingSystem: Microsoft Windows 10.0.14393
OsBitVersion: x64
Is64BitProcess: True
CurrentUser: NT AUTHORITY\SYSTEM
MachineName: 1ODS-APP01-DC1
ProcessorCount: 6
CurrentDirectory: C:\WINDOWS\system32
TempDirectory: C:\WINDOWS\TEMP
HostProcessName: Octopus.Server
PID: 4636
May 26th 2020 11:38:27Verbose
Executing Deploy ispac SSIS project from Referenced Package (type Run a Script) on 7csm-dbs01-dc1
May 26th 2020 11:38:27Verbose
Using Calamari 11.1.2
May 26th 2020 11:38:27Verbose
Using Calamari.linux-x64 11.1.2
May 26th 2020 11:38:27Verbose
Starting C:\Windows\system32\WindowsPowershell\v1.0\PowerShell.exe in working directory ‘D:\Octopus\Work\20200526163826-117478-148’ using ‘OEM United States’ encoding running as ‘LITTLER-US_OctopusDeploy’ with the same environment variables as the launching process
May 26th 2020 11:38:27Verbose
Calamari Version: 11.1.2
May 26th 2020 11:38:27Verbose
Environment Information:
May 26th 2020 11:38:27Verbose
OperatingSystem: Microsoft Windows NT 6.3.9600.0
May 26th 2020 11:38:27Verbose
OsBitVersion: x64
May 26th 2020 11:38:27Verbose
Is64BitProcess: True
May 26th 2020 11:38:27Verbose
CurrentUser: LITTLER-US_OctopusDeploy
May 26th 2020 11:38:27Verbose
MachineName: 7CSM-DBS01-DC1
May 26th 2020 11:38:27Verbose
ProcessorCount: 4
May 26th 2020 11:38:27Verbose
CurrentDirectory: D:\Octopus\Work\20200526163826-117478-148
May 26th 2020 11:38:27Verbose
TempDirectory: C:\Users_OctopusDeploy\AppData\Local\Temp\
May 26th 2020 11:38:27Verbose
HostProcess: Calamari (5880)
May 26th 2020 11:38:28Verbose
Considering ‘SSIS Package’ for extraction
May 26th 2020 11:38:28Info
Extracting package ‘D:\Octopus\Files\CaseSmart_FTA_Payment_SSIS@S1.44@044FCC2BAEEACC4DB857438AD49338B6.nupkg’ to ‘D:\Octopus\Work\20200526163826-117478-148\SSIS Package’
May 26th 2020 11:38:28Verbose
Performing variable substitution on ‘D:\Octopus\Work\20200526163826-117478-148\Script.ps1’
May 26th 2020 11:38:28Verbose
Executing ‘D:\Octopus\Work\20200526163826-117478-148\Script.ps1’
May 26th 2020 11:38:28Verbose
Setting Proxy Environment Variables
May 26th 2020 11:38:29Verbose
Name Value
May 26th 2020 11:38:29Verbose

May 26th 2020 11:38:29Verbose
PSVersion 5.1.14409.1018
May 26th 2020 11:38:29Verbose
PSEdition Desktop
May 26th 2020 11:38:29Verbose
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
May 26th 2020 11:38:29Verbose
BuildVersion 10.0.14409.1018
May 26th 2020 11:38:29Verbose
CLRVersion 4.0.30319.42000
May 26th 2020 11:38:29Verbose
WSManStackVersion 3.0
May 26th 2020 11:38:29Verbose
PSRemotingProtocolVersion 2.3
May 26th 2020 11:38:29Verbose
SerializationVersion 1.1.0.1
May 26th 2020 11:38:30Verbose
PowerShell Environment Information:
May 26th 2020 11:38:30Verbose
OperatingSystem: Microsoft Windows NT 6.3.9600.0
May 26th 2020 11:38:30Verbose
OsBitVersion: x64
May 26th 2020 11:38:30Verbose
Is64BitProcess: True
May 26th 2020 11:38:30Verbose
CurrentUser: LITTLER-US_OctopusDeploy
May 26th 2020 11:38:30Verbose
MachineName: 7CSM-DBS01-DC1
May 26th 2020 11:38:30Verbose
ProcessorCount: 4
May 26th 2020 11:38:30Verbose
CurrentDirectory: D:\Octopus\Work\20200526163826-117478-148
May 26th 2020 11:38:30Verbose
CurrentLocation: D:\Octopus\Work\20200526163826-117478-148
May 26th 2020 11:38:30Verbose
TempDirectory: C:\Users_OctopusDeploy\AppData\Local\Temp\
May 26th 2020 11:38:30Verbose
HostProcess: powershell (3388)
May 26th 2020 11:38:30Verbose
TotalPhysicalMemory: 12582388 KB
May 26th 2020 11:38:30Verbose
AvailablePhysicalMemory: 2800440 KB
May 26th 2020 11:38:30Error
ParserError: At D:\Octopus\Work\20200526163826-117478-148\Script.ps1:68 char:132
May 26th 2020 11:38:30Error

… dulePath -Exclude msv*.dll | Where-Object {$_.Extension -eq “.dll”}))
May 26th 2020 11:38:30Error

May 26th 2020 11:38:30Error
Missing statement body in foreach loop.
May 26th 2020 11:38:30Error
At D:\Octopus\Work\20200526163826-117478-148\Script.ps1:68 char:132
May 26th 2020 11:38:30Error

… dulePath -Exclude msv*.dll | Where-Object {$_.Extension -eq “.dll”}))
May 26th 2020 11:38:30Error

May 26th 2020 11:38:30Error
at , D:\Octopus\Work\20200526163826-117478-148\Bootstrap.Script.ps1: line 2759
May 26th 2020 11:38:30Error
at , : line 1
May 26th 2020 11:38:30Error
at , : line 1
May 26th 2020 11:38:30Verbose
Adding journal entry:
May 26th 2020 11:38:30Verbose

May 26th 2020 11:38:30Verbose

May 26th 2020 11:38:30Verbose

May 26th 2020 11:38:30Error
Access to the path is denied.
May 26th 2020 11:38:30Error
System.UnauthorizedAccessException
May 26th 2020 11:38:30Error
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
May 26th 2020 11:38:30Error
at System.IO.File.InternalReplace(String sourceFileName, String destinationFileName, String destinationBackupFileName, Boolean ignoreMetadataErrors)
May 26th 2020 11:38:30Error
at Calamari.Integration.FileSystem.CalamariPhysicalFileSystem.OverwriteAndDelete(String originalFile, String temporaryReplacement)
May 26th 2020 11:38:30Error
at Calamari.Deployment.Journal.DeploymentJournal.AddJournalEntry(JournalEntry entry)
May 26th 2020 11:38:30Error
at Calamari.Deployment.Journal.DeploymentJournalWriter.AddJournalEntry(RunningDeployment deployment, Boolean wasSuccessful, String packageFile)
May 26th 2020 11:38:30Error
at Calamari.Commands.RunScriptCommand.Execute(String commandLineArguments)
May 26th 2020 11:38:30Error
at Calamari.Program.Run(CommonOptions options)
May 26th 2020 11:38:30Error
at Calamari.Program.Main(String args)
May 26th 2020 11:38:30Verbose
Process C:\Windows\system32\WindowsPowershell\v1.0\PowerShell.exe in D:\Octopus\Work\20200526163826-117478-148 exited with code 100
May 26th 2020 11:38:30Verbose
Updating manifest with output variables
May 26th 2020 11:38:30Verbose
Updating manifest with action evaluated variables
May 26th 2020 11:38:30Fatal
The remote script failed with exit code 100
May 26th 2020 11:38:30Fatal
The action Deploy ispac SSIS project from Referenced Package on 7csm-dbs01-dc1 failed

Shawn_Sesna · 26 May 2020 17:26

Hey there! It looks like we may have accidentally introduced a syntax error. Let’s try this, remove what you’ve added and replace the function Load-SqlServerAssemblies with this,

Function Load-SqlServerAssmblies
{
	# Declare parameters
    
	# Get the folder where the SqlServer module ended up in
	$sqlServerModulePath = [System.IO.Path]::GetDirectoryName((Get-Module SqlServer).Path)
    
    # Loop through the assemblies
    foreach($assemblyFile in (Get-ChildItem -Path $sqlServerModulePath -Exclude msv*.dll | Where-Object {$_.Extension -eq ".dll"}))
    {
        # Load the assembly
        [Reflection.Assembly]::LoadFile($assemblyFile.FullName) | Out-Null

        Write-Host "Loading assembly $($assemblyFile.FullName)"
    }    
}

ncase · 27 May 2020 14:25

Hello,
After reverting the previous changes to the script and adding the above function load-sqlserverassebiles the deployment is producing the attached errors. ServerTasks-117514.log.txt (24.4 KB)

Shawn_Sesna · 27 May 2020 14:58

Thank you very much for doing that, that part can be removed. I’m still trying to figure out where the “A drive with the name ‘SQLSERVER’” is coming from and hoping it’s not a red herring. If you’re still open to doing some more debugging, I’d like to try a couple of other things.

On the server that is doing the deployment, would you be able to get me a list of installed modules that begin with SQL?

Get-Module -ListAvailable | Where-Object {$_.Name -like “sql*”}

In your copy of the template, try commenting out the following two lines (or just remove them)

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue

Thank you again for bearing with us as we try to track this down.

ncase · 4 June 2020 18:39

PS C:\WINDOWS\system32> Get-Module -ListAvailable | Where-Object {$_.Name -like “sql*”}

Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version Name ExportedCommands

Manifest 20.0 SqlServer {Add-SqlColumnEncryptionKeyValue, Complete-SqlColumnMaster…

Directory: D:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules

ModuleType Version Name ExportedCommands

Manifest 2.0 SQLASCMDLETS {Add-RoleMember, Backup-ASDatabase, Invoke-ASCmd, Invoke-P…
Manifest 1.0 SQLPS {Add-SqlColumnEncryptionKeyValue, Complete-SqlColumnMaster…

Directory: C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\Modules

ModuleType Version Name ExportedCommands

Script 3.1.17… SqlChangeAutomation {Export-DatabaseBuildArtifact, Export-DatabaseReleaseArtif…
Above is what is on the worker doing the deployment.
Thank you,

jeremy.miller · 4 June 2020 20:23

Hi Nate,

Could you please try a couple more things for us?

Can you remote into the worker and run the following commands in a fresh powershell instance and paste the results for us? The first is psdrive, the second is get-module.

We think theres a possibility that all powershell instances on this worker already have a sqlserver drive setup in powershell from a global configuration and that may be causing the issue.

Please let me know what the results are when you get a chance to test.

Thanks,
Jeremy

jeremy.miller · 17 June 2020 14:54

Hi Nate,

The SSIS template has been updated to resolve the issue. Can you please go ahead and sync the updated version and apply it to your project then run a test and let me know if it works?

Thanks,
Jeremy

system · 25 July 2020 14:18

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