Method not found Microsoft.SqlServer.Management.IntegrationServices.EnableSsisSupportAlwaysOnSqmHelper.Initialize()

Today, working with SSIS on a SQL Server 2016 RTM I wasn’t able to create the SSIS Catalog.

The error I got was :

Method not found: ‘Void Microsoft.SqlServer.Management.IntegrationServices.EnableSsisSupportAlwaysOnSqmHelper.Initialize()’. (Microsoft.SqlServer.IntegrationServices.UITasks)

Looking at the documentation for the namespace Microsoft.SqlServer.Management.IntegrationServices I quickly figured out that I would be able to create the SSIS Catalog manually using PowerShell.

But then I couldn’t locate the Microsoft.SqlServer.Management.IntegrationServices dll anywhere except from in the GAC so I had to load it a somewhat cumbersome way (with help from Remo). Below is the script I used for doing that.

First portion is for enabling CLR like it does in the SSMS dialog:

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'clr enabled', 1; 
GO 
RECONFIGURE; 
GO

The second part loads the assembly.

$assemblyPath = 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll'
$fullName = [System.Reflection.AssemblyName]::GetAssemblyName($assemblyPath).FullName
[System.Reflection.Assembly]::Load($fullName)

Then it’s only a matter of connecting to the server and creating the catalog manually using the Catalog method

#Because I am lazy and do not want to retype this long namespace everytime
$IntegrationServicesNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

#Connect to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;" 
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

#Create the catalog
$integrationServices = New-Object $IntegrationServicesNamespace".IntegrationServices" $sqlConnection
$catalog = New-Object $IntegrationServicesNamespace".Catalog" ($integrationServices, "SSISDB", "P@ssw0rd") 
$catalog.Create()

After that I was able to use my newly created SSIDB Catalog.

Happy SSIS’ing

Advertisements

Tags: EnableSsisSupportAlwaysOnSqmHelper, Powershell, ssis catalog