SqlIntegrationServicesCatalog: Resource to perform the initial creation of the Integration Services Catalog about sqlserverdsc HOT 5 OPEN

I can see a public command for this. You mention a resource but it looks like you mean a command? Maybe you aiming for both?

Would be great if we can avoid using Load() or LoadWithPartialName() since it will just return the first version if finds in the GAC, which can be the wrong version if multiple SQL Server major versions are installed.

It would be better if the type was available by just running Import-Module.

Import-Module -Name 'SqlServer' # or 'dbatools' might work?
$smoServer = Connect-SqlDscDatabaseEngine -ServerName 'testclu01a' -InstanceName 'SQL2014'
# If the correct assembly is missing in the module SqlServer (or dbatools) this call fails.
$integrationServices = New-Object -TypeName 'Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices' -ArgumentList @($smoServer)

from sqlserverdsc.

If using Import-Module as mentioned in previous comment fails then as an alternative we should aim to use Load() to make sure the correct version of the assembly is loaded. See example:

Import-Assembly is an internal function

from sqlserverdsc.

Ideally it would be a resource. So could do something like the following in DSC to create an Integration Services Catalog.

SqlIntegrationServicesCatalog 'SSISDB' {
ServerName = 'localhost'
InstanceName = 'MSSQLSERVER'
CatalogPassword = $secret
Ensure = 'Present'
}

from sqlserverdsc.

For now I am utilizing the Microsoft documented commands in a Script Task. Though oddly fails for SQL 2022, but works for all other versions of SQL which I have tested (2019, 2016).

With SQL 2022 only, it produces this error and only when run in DSC. If executed manually on the server it works.

from sqlserverdsc.

Would be great if this would be a public commands (Get-, Test-, New-), then the public commands can be used in a class-based resource.

The error you seeing can be the result of conflicting assemblies. That looks like a typical error for those scenarios when using the Load() methods. Important that all resources use the correct version of the same assemblies. But I'm just guessing, it could be another problem.

from sqlserverdsc.