Did support for SSIS disappear?

[micbarrett]

The latest version of the SqlServer Powershell module seems to be missing support for SSIS. The documentation here:

https://learn.microsoft.com/en-us/sql/powershell/sql-server-powershell-provider?view=sql-server-ver16

Still lists SQLSERVER:\SSIS as one of the ways to navigate SSISDB in a SQL Server instance. However, the support is seemingly gone from the module itself. Any explanation here?

Thanks.

[Matteo-T]

Hi @micbarrett, the SSIS: root was removed while I was doing the cleanup in v22.

The SSIS: root had its set of issues (as mentioned in https://github.com/microsoft/SQLServerPSModule/wiki/What's-new-in-v22#relevant-changes-in-v22-compared-to-v21) so I decided to leave it out and see if anybody was really missing it.

The fact that it relies on packages/DLLs that are not available on nuget.org made it look like yet another hurdle in the road to making this module open source (though, it may not be the only one - so, we may have to settle for a hybrid solution where "some functionality of the module won't be open source", hence that could open up the door to have the SSIS: root back).

In addition to that, there could be incompatibility with the new Microsoft.Data.SqlClient driver that I'd have to figure our and possible work with the right Team here at Microsoft to see what options we have.

So, all in all, it's non-trivial work... I'm curious to know if you (and others) feed really strong about its removal.

[ChristopherMank]

Hi @Matteo-T,

Thanks for the note. We currently have a PowerShell script that we use in an Azure pipeline task to deploy SSIS packages. We use the SSIS root as it is the first example given in the below documentation. We currently cannot upgrade to the latest module version since the SSIS root was removed. There is a second example given which looks similar. Do you know if that method still works in the latest version? If so, then we can refactor. If not, we absolutely need a solution for it.

https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-powershell?view=sql-server-ver16

Thanks,

Christopher

[Matteo-T]

Hi @ChristopherMank,

The second example (provided you have the DLLs available and you update the version number from 14.0.0.0 to whatever you have available/need) should still work. If you run PS on a machine with SSIS installed, the required DLLs should be (in the GAC).

Let me take a look at the whole thing one more time and see what we can do about it...

[ChristopherMank]

Thanks, appreciate it! We run this PowerShell script/task on self-hosted agents for Azure DevOps (and soon GitHub actions). Given that, we do not have SQL Server or SSIS loaded. We also don't want to go down that road as it would further enlarge our process to build/maintain our agent platform.

[dbaduck]

I am OK with having the SSIS root removed as long as there is good documentation on how you get access to the Catalog in an object which will then allow SSIS project deploy. I tried very many things to get the catalog using Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices and there are many different classes that report odd things like trying to create the object with a Server object as in the documentation just to get an error that there are no constructors with 1 argument.

I reverted to using the v21 of SqlServer so that I could get the SSIS root, but would love to be able to use the latest and have an alternative solution with guidance on how to get the catalog reference correctly.

Thanks for all your work.

[Matteo-T]

Hi @dbaduck (aka Ben)... I was talking to @erinstellato-ms today and she reminded me about this issue.

Would you be willing to share a bit of the scenarios you want back so I can see what I can do about it, ideally without having to bring back that old DLL (it's really legacy stuff that I'm not thrilled about... also not available on nuget.org, this being a little bit of a hurdle to going OSS eventually...)

Try to use simple terms as I'm not much of an SSIS expert. Feel free to send your stuff directly to me/Erin.