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
[…] Regis Baccaro ran into a rather lengthy error when trying to create an SSISDB catalog: […]