Loading SQL Server PowerShell Module tip
As mentioned in previous posts:
I have been working on integrating our SSIS projects into our continuous delivery pipeline using Octopus, PowerShell and the SQL Server PowerShell module. In this post I will cover how I had to resolve the following error: New-Object : Cannot find type [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]: verify that the assembly containing this type is load.
I'm going to start explaining the contributing factors which led me to getting the aforementioned error:
As detail in the image below:
My script used the following example, which is recommended on many sites on the internet:
The problem was with the else block it assumes that if the module is installed then the SQL Server assemblies will be in the Global Assembly Cache (GAC). In my case, they were not as my application server didn’t have any component of SQL Server installed until the module.
Once my script was updated the deployment code was working again and now completes in less than 5 minutes.
Here is a PowerShell Script template that will load the SQL Server module and assemblies either by download a temporary copy or using the installed module: Load-SQLServerModule.ps1
I have been working on integrating our SSIS projects into our continuous delivery pipeline using Octopus, PowerShell and the SQL Server PowerShell module. In this post I will cover how I had to resolve the following error: New-Object : Cannot find type [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]: verify that the assembly containing this type is load.
I'm going to start explaining the contributing factors which led me to getting the aforementioned error:
- Where the script is executed
- What the script does.
Where the script is executed
As part of the deployment pipeline, we decided not to install tentacles on our SQL Servers. To deploy our SQL Server changes we use one of our application servers, to receive Octopus packages like our SSIS projects, SQL Scripts, then the application server runs the deployment PowerShell scripts to update our SQL Servers.As detail in the image below:
 |
| Our Deployment Pipeline |
What the script does
The script does the following steps:- Check for the installation of the SQL Server PowerShell module
- If the module is not installed then download a temporary copy and load all the module assemblies for the current PowerShell session.
- Connect to the SSIS server
- Create the SSIS folder if it didn't exist.
- Upload the SSIS ISPAC files to the server.
- Then linked the environment configuration and SSIS project together
- Then create/update the environment configuration
How I got the error
To optimise our deployment pipeline, I decided to manually install the SQL Server PowerShell module on our application server to stop the several downloads of the module. Afterwards I started getting the error: New-Object : Cannot find type [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]: verify that the assembly containing this type is load.My script used the following example, which is recommended on many sites on the internet:
1 2 3 4 5 6 7 8 9 10 11 12 | if ((Get-SqlModuleInstalled -PowerShellModuleName "SqlServer") -ne $true) { # Display message Write-Output "PowerShell module SqlServer not present, downloading temporary copy ..." # Download and install temporary copy Install-SqlServerPowerShellModule -PowerShellModuleName "SqlServer" -LocalModulesPath $LocalModules # Dependent assemblies Get-SqlServerAssemblies } else { # Load the IntegrationServices Assembly [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null # Out-Null suppresses a message that would normally be displayed saying it loaded out of GAC } |
The problem was with the else block it assumes that if the module is installed then the SQL Server assemblies will be in the Global Assembly Cache (GAC). In my case, they were not as my application server didn’t have any component of SQL Server installed until the module.
How I resolve the error
To resolve the error, I had to update my script to always load the assemblies from the installed location of SQL Server PowerShell module. I managed this by changing my script to always call Get-SqlServerAssemblies but tweak the function to use -ListAvailable when calling the Get-Module for the SQLServer. This enable getting the path to were the module is being loaded from, as shown in code below:1 2 3 4 5 6 7 8 9 10 11 12 | Function Get-SqlServerAssmblies { # Declare parameters # Get the folder where the SqlServer module ended up in $sqlServerModulePath = [System.IO.Path]::GetDirectoryName((Get-Module -ListAvailable -Name "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 } } |
Once my script was updated the deployment code was working again and now completes in less than 5 minutes.
Here is a PowerShell Script template that will load the SQL Server module and assemblies either by download a temporary copy or using the installed module: Load-SQLServerModule.ps1
Comments