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 executedAs 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 doesThe 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 errorTo 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:
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 errorTo 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:
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