How do I deploy SSIS packages with Powershell?

Question

How do I deploy SSIS packages with Powershell?

Oblio 51

I'm trying to follow the directions on this guide: https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-powershell?view=sql-server-ver15

The prerequisites talk about ports, but they fail to mention what I need installed on my machine to execute the script. Can someone help with that? Let's suppose I just checked out a project with SSIS packages. What do I need to do to run the powershell script to deploy to a server?

2 answers

Your answer

Answer 1

Michael Taylor 60,161

Depends upon which approach you're taking. After the Supported authentication method section it shows you 2 different ways to do the same thing. The first approach is using the SSIS Powershell provider. The installation is available here.

The second approach is just using raw PS and doesn't require anything extra. It does assume you're running SSIS on the same machine as it dynamically loads the needed SSIS binaries to do its work. However note that it requires a full assembly name and therefore where it is using Assembly.Load to load the SSIS binaries it requires that you set the Version= value to the correct version for your instance of SSIS. Other than that it is pure PS.

Oblio 51 Reputation points

2022-03-30T20:33:56.047+00:00

Ok, so, let's assume that I'm not developing code on the SQL server (seems like a poor practice). I suppose that means I've got to use the first approach.

It says I need to install the SQL Server Powershell; and that I should use SqlServer module not SQLPS. To do that, I just need to run Install-Module -Name SqlServer, right? Because I've already done that, and cannot run that script.
Michael Taylor 60,161 Reputation points

2022-03-30T20:55:24.98+00:00
I suppose that means I've got to use the first approach.

No you can use either approach in most cases. The only thing you need to do is have SSIS installed, it doesn't have to actually be the SSIS machine you're targeting. In both approaches you specify the server/instance you want to install to. The SQL Powershell provider is just a wrapper around the longer PS script. But to run the second version you need to install SSIS on your machine. Most developers are running SQL Server Developer already and you likely already have SSIS installed so that is sufficient. If you have SSDT installed then it is probably already set up in fact. Try running that load command directly from PS. If it works, adjusting the version for your instance, then you're good to go.

To install the SQL provider you first need to install it from PS Gallery using the Install-Module command (one time thing).

Install-Module -Name SqlServer -AllowClobber

You need to start PS as an admin to install modules. You should also be trusting the PS Gallery otherwise you might get an error about trust.

Now you need to load the module so the drive is available.

Load-Module SqlServer Get-PSDrive
Oblio 51 Reputation points

2022-03-30T21:38:56.18+00:00
At risk of fracturing the thread too much:

"having SSIS installed" means having SQL Server installed. I'm developing on my workstation, not the target server.

I have SSDT installed (VS2019); it does not provide the PS modules I need.

"Load-Module" gives me a "no such cmdlet" error.
Michael Taylor 60,161 Reputation points

2022-03-30T21:54:06.72+00:00

1) Yes you can install the free version of SQL Server (Developer) for local testing if you want. Most developers do but that has no actual bearing on the target server for your SSIS work. For example I have SQL/SSIS installed on my local machine so I can set up temp DBs and run test SSIS packages without connecting to our dev SQL server. But I deploy to our dev SQL server instance.

2) PS doesn't ship with SSDT. What did you get when you tried using the Assembly.Load call in PS for your specific SSIS version?

3) That means you haven't installed the cmdlet from PS Gallery yet. What do you get when you run Get-Module SqlServer -ListAvailable. If it shows nothing then you need to install it first using Install-Module.

Also please clarify what version of PS you're running.
Oblio 51 Reputation points

2022-03-30T22:18:07.937+00:00
Yes, this is what I mean, and what I don't want to do. I want to develop a process that does not require the server and all that to be installed.

When I run [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") in PS7, I get "v4.0.30319 C:\Program Files\PowerShell\Modules\SqlServer\21.1.18256\Microsoft.SqlServer.Management.Integrat…"
When I run [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") in PS5, I get "v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SQLServer.Management.IntegrationServices\11.0.0.0__8984..."

When I run "Get-Module SqlServer -ListAvailable", I get "21.1.18256 SqlServer"

I have both PS5 (default with Win10) and PS7. I'd prefer to use PS7 if I can.
Michael Taylor 60,161 Reputation points

2022-03-30T22:55:17.41+00:00

1) Since you have the assemblies it appears that all you really need is SSDT installed. Now that may or may not be an issue. If you're looking to do this on a build server then you'll have to ensure they are there. Otherwise you might do better just using the SQL Powershell provider as it can be installed on a build server easily.
2) That means you have the binaries so you can use the second approach with pure PS. But again you could use the SQL PS Provider to make it easier. Whichever approach you want to take will work.
3) That means you have the provider already installed. Load the provider in your PS session using Load-Module SqlServer and then Get-PSDrive to confirm SQLServer is listed as a drive.

This should work in either PS or PS Core. So, again, which approach you use is up to you in this case.

Answer 2

Oblio 51

SSDT is no longer a separate install, but is acquired through the VS installer. I've done that.
I know where the SqlServer Module came from, but not the assemblies in the GAC.
So, I still don't have a command Load-Module...

Michael Taylor 60,161 Reputation points

2022-03-31T14:16:16.45+00:00
1) Yes and no. SSDT is part of VS if you install the workload however it does not include SSRS or SSIS support. Those are 2 separate extensions that have to be added separately after VS is installed.
2) I don't know who is dropping the assemblies onto your machine. It is quite possible it is SSDT as part of the VS installation but that would definitely seem odd given that SSIS support is provided through a separate component. It might have been installed as part of the SMO installation.
3) Sorry, that should have been Import-Module.

$server = "your server or localhost" $instance = "your instance" Import-Module SqlServer Get-PSDrive Get-Item "SQLSERVER:\SSIS\$server\$instance\catalogs\ssisdb\"

We've been focusing on PS and SSIS but I should at least mention that if you're working on a CI/CD for SSIS and you use Azure DevOps then there are 3rd party tasks that can handle deployment for you. If you just want to deploy from VS then it can already do that. You'd only need PS if you wanted to do other things as well.
Oblio 51 Reputation points

2022-04-04T19:42:49.133+00:00
Right, so this circles back to the "what are the prerequisites to run this code" part of the question. I think I found the SSIS extension on the VS marketplace, but I don't know if that's what I need/am looking for.

The who is me. I'm trying to work out what I need installed in order to run a deploy.

At risk of fracturing this further, I don't know what the Get-PSDrive is for. My script more closely mirrors the tutorial one with $integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection

Finally, yes, I will eventually want to use CI/CD to deploy it, but that's more complicated and I still don't know what's required to build and deploy. I figured it'd be easier to get it running on my machine before trying to automate it in the cloud.
Michael Taylor 60,161 Reputation points

2022-04-04T20:32:47.697+00:00
Sounds like you want to rely on the SSIS assemblies already being on the machine rather than using the provided SSIS Powershell cmdlets that MS provides.

I cannot confirm without starting up a clean machine but installing SSMS should be sufficient to get the necessary assemblies. You don't need VS (and therefore the SSDT extensions) installed to just deploy SSIS. PS wouldn't use it even if it were installed.

Once SSMS is installed look in the GAC of the machine to get the latest version of the assemblies it dropped for SSIS (e.g. Microsoft.SQLSErver.Management.IntegrationServices). In the example script it is 14.0.0.0 but your version might be different and you must use the correct version that was installed on your machine otherwise the call will fail.
Get-ChildItem c:\windows\assembly -Include 'Microsoft.SqlServer.Management.IntergrationServices' -recurse

Get-PSDrive isn't needed if you aren't using the cmdlets provided by MS.

For CI/CD, it depends on your build server. SSIS has support in Azure DevOps without Powershell IIRC.
Oblio 51 Reputation points

2022-04-05T14:50:57.37+00:00
Well, I guess that's part of my struggle. The quickstart guide just says "run the script" without providing what else is needed. I do have SSMS (v18) installed.

I understand the version issue, so we'll set that aside. My issue is more with how PS finds the correct dependencies when instructed to load. For example when I load v14 in PS7, it's not found/loaded from the GAC. And, although I can load it, the script throws an error when trying to use it. Separately, in PS5, I can load v11-13 (they're in the GAC), but I can't use them as I get a "Could not load file or assembly 'Microsoft.SqlServer.Dmf, Version=11.0.0.0" error.

Regarding CI/CD, I thought the build server was part of the pipeline.
Michael Taylor 60,161 Reputation points

2022-04-05T15:21:26.78+00:00

Since you're using the pure PS approach let's walk through the script that MS posted as an example. Hopefully that will explain what is going on so you can diagnose your issue. Just so we're on the same page note that PS 1-5.x is based upon .NET Framework. PS Core/7 is based upon .NET Core. Therefore you can see differences as they are using 2 different runtimes.

The Load the IS assembly line is loading the assembly/binary needed to manage SSIS. This is necessary because it is not available by default. It is using the full name which includes a version #. If the runtime cannot find that version then it will fail to load.

The next set of lines sets up a connection to your SQL Server instance. This is needed by SSIS.

Then it creates an instance of the IntegrationServices type that is the root of SSIS and uses the connection you previously defined.

Next it gets the SSISDB catalog from your SSIS instance.

Now it creates a new folder to store your package in by creating an instance of CreateFolder and passing it the catalog, the folder to create and a description. Then it calls Create to create the folder.

Lastly it opens the project file and deploys the project to the folder.
Michael Taylor 60,161 Reputation points

2022-04-05T15:24:40.083+00:00
(continuation)

Deploying the project would include the data sources and packages. If you want to deploy things manually (or you don't have a project) then you can also use DeployPackages. But in this case if you are using shared data sources then you'd need to deploy them as well.

PS, by default, tends to continue executing even if something goes wrong. To prevent this consider setting the error action preference at the top of the script.

$ErrorActionPreference = "Stop"

Note that some commands may not work and not report an error so it is always good to check the return values of methods as well. Alternatively you could use a try-catch to deal with errors.
Michael Taylor 60,161 Reputation points

2022-04-05T15:28:11.59+00:00

As for how PS finds assemblies that it is just using the .NET runtime and that process is discussed here. Since you are using a fully qualified name the runtime first looks in the GAC which is system wide. If it doesn't find it there then it'll look locally but it must find an exact match to the version. For SSIS the assemblies should be in the GAC so it should always find it there. You can use the command I posted earlier to look in the GAC for your version of PS to see what it finds.
Oblio 51 Reputation points

2022-04-05T15:36:19.353+00:00
Yeah, I follow what it's doing. I have a whole script that will take care of the deployment for me. However, if I isolate just the connection piece, this is what's failing:

[System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices" $databaseServer = "nameofserver" $sqlServerConnection = New-Object System.Data.SqlClient.SqlConnection "Data Source=$databaseServer;Initial Catalog=master;Integrated Security=SSPI;" $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlServerConnection

At this point, I get the "Dmf" error. When I try the same in PS7, I get "New-Object: Exception calling ".ctor" with "1" argument(s): "The type initializer for 'Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices' threw an exception."
Michael Taylor 60,161 Reputation points

2022-04-05T16:30:27.697+00:00
The SSIS type is failing to initialize properly. Unfortunately the exception is just a wrapper so you need to get to the inner exception. You can use this code after that line

$Error[0].Exception

Then look at the inner exceptions. In my PS Core version it cannot find SqlContext from System.Data. In PS 5.x it fails when finding Dmf.Common v13. Unfortunately it appears several people are having issues with Dmf to the point MS said it was an internal assembly which shouldn't be used but that would conflict with what the deployment instructions mention.
Michael Taylor 60,161 Reputation points

2022-04-05T16:41:59.897+00:00

I did find one old reference mentioning that installing the SQL Server Feature Pack which might be this one. Not 100% sure on it.
Oblio 51 Reputation points

2022-04-05T20:11:07.697+00:00

Yes, when I try to run that in PS7, mine is also "Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data". Not sure what that means or where to get it loaded.

The Dmf.Common assembly is not in the IntegrationServices GAC, but there is one in NativeImages_v4.0.30319_64.
Michael Taylor 60,161 Reputation points

2022-04-05T21:34:59.19+00:00

It looks like, starting with SQL2017 that the SMO assemblies are no longer in the GAC and if they are missing then things fail. Unfortunately I don't see a way around it other than pulling down the binaries from NuGet. But even trying to do that in PS resulted in the same error. Seems like this is catch-22 at this point.
Oblio 51 Reputation points

2022-04-06T00:36:52.863+00:00

Which brings me back to the original question: how do I follow the guide supplied by Microsoft? The prerequisites are lacking.
Michael Taylor 60,161 Reputation points

2022-04-06T03:32:38.807+00:00

I agree. I think you should provide feedback here to let them know the instructions do not work in PS for SQL 2017+. Until they provide better documentation then the PS Provider may be your only option. Fortunately it can be installed standalone so it should work in a build-like environment like you want to eventually target.
Oblio 51 Reputation points

2022-04-06T14:41:30.913+00:00

So, where do I get the PS Provider?
Michael Taylor 60,161 Reputation points

2022-04-06T14:45:54.627+00:00
It comes from PS Gallery. That is what I posted in my original responses. You install from the gallery, import the module and then you can use it.

Install-Module -Name SqlServer -AllowClobber Import-Module SqlServer

Oblio 51

My apologies for being slow here, but I'm not getting it; I think I need more steps. Should the script look like this?

Install-Module -Name SqlServer -AllowClobber
Import-Module SqlServer

$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$databaseServer = "nameofserver"
$sqlServerConnection = New-Object System.Data.SqlClient.SqlConnection "Data Source=$databaseServer;Initial Catalog=master;Integrated Security=SSPI;"
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlServerConnection

Michael Taylor 60,161 Reputation points

2022-04-06T16:03:33.637+00:00
No, you're combining the PS Provider approach with the raw SSIS approach. You'll need to use one or the other and since you're having an issue getting SSIS assemblies loaded I think the PS Provider is the approach you'll want for now. In the installation guide that you originally linked to they had a sample script that uses the PS provider. You won't be creating SQL connections directly but using the SQLSERVER:\ "file system" to deploy the SSIS package. Other than initial setup it basically does the same thing as the raw script in that it gets the catalog, creates a folder, etc.

Import-Module SqlServer Get-ChildItem SQLSERVER:\

Note that on my machine PS 7 doesn't return SSIS but PS 5 does so I'm going to assume that you're using PS 5 right now.

Also note that you must have an SSIS catalog defined (check SSMS). For example if you have a catalog called SSISDB on your local machine then you could get to it using this: Get-Item SQLSERVER:\SSIS\localhost\default\Catalogs\SSISDB. From there you can create a folder and then deploy based upon the instructions in the docs.
Oblio 51 Reputation points

2022-04-06T16:30:20.533+00:00

Well, PS5 does not recognize Install-Module, so I won't be able to use that.

In PS7, when I use Get-Item SQLSERVER:\SSIS\$databaseServer\default\Catalogs\SSISDB, it says the path does not exist.
Michael Taylor 60,161 Reputation points

2022-04-06T16:43:46.537+00:00

Install-Module is supported in PS 5, in all PS versions in fact. What error are you getting?

PS7 doesn't seem to work with SSIS right now. That would be a discussion for the SSIS team I believe.
Oblio 51 Reputation points

2022-04-06T16:45:55.777+00:00

"Install-Module : The term 'Install-Module' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again."
Michael Taylor 60,161 Reputation points

2022-04-06T16:47:15.257+00:00
Are you running PS 5.1? It is built in.

$PSVersionTable

Oblio 51

It's whatever comes with Win10:

Name                           Value
----                           -----
PSVersion                      5.1.19041.1320
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.19041.1320
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

Oblio 51 Reputation points

2022-04-06T16:51:52.087+00:00

It supports Install-Package, but not Install-Module.
Michael Taylor 60,161 Reputation points

2022-04-06T16:56:43.557+00:00
Something is seriously wrong then. Install-Module has been supported since PS 5.0 and it works out of the box. It is provided by the PowershellGet module which is part of the PS installation.

get-module Powershellget

If that returns nothing then

import-module Powershellget get-module powershellget get-help install-module
Oblio 51 Reputation points

2022-04-06T17:10:54+00:00
While I'd love to solve this, I don't want to focus on it too much when Install-Package appears to perform the same function.

Once installed, I was able to do

Import-Module SqlServer $catalog = Get-Item SQLSERVER:\SSIS\$databaseServer\default\Catalogs\SSISDB

and the rest of my deploy script ran as expected.

Over all the process is exceptionally frustrating. I don't know why I can't just "install and go". I'd also love to have support for PS7 since it appears to be becoming mainstream.

Share via

How do I deploy SSIS packages with Powershell?

2 answers

Your answer