SqlIntegrationServicesCatalog: Resource to perform the initial creation of the Integration Services Catalog

[SQLCanuck]

Resource proposal

Microsoft has documented how to problematically create the SSIS Catalog here https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver16#to-create-the-ssisdb-catalog-programmatically

It would be beneficial if the SqlServerDsc was able to incorporate this additional capability, to automate another part of configuring a SQL Server.

Proposed properties

Property	Type qualifier	Data type	Description	Default value	Allowed values
CatalogPassword	Password	String	Password utilized to encrypt the Integration Services Catalog	None	None

Special considerations or limitations

None.

[johlju]

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)

[johlju]

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:

SqlServerDsc/source/DSCResources/DSC_SqlReplication/DSC_SqlReplication.psm1

Line 841 in 026647c

$connectionInfo = Import-Assembly -Name "Microsoft.SqlServer.ConnectionInfo, Version=$SqlMajorVersion.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

Import-Assembly is an internal function

SqlServerDsc/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1

Line 822 in 026647c

function Import-Assembly

[SQLCanuck]

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'
}

[SQLCanuck]

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.

[johlju]

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.