SSIS deployment through powershell 7

Question

SSIS deployment through powershell 7

Oblio 1

I am trying to follow the instructions here - https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-powershell?view=sql-server-ver15

I get this error: MethodInvocationException: Exception calling "Load" with "1" argument(s): "Could not load file or assembly 'Microsoft.SQLServer.Management.IntegrationServices, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL'. Operation is not supported. (0x80131515)"

Perhaps the instructions are missing a prerequisite that explains how to install something?

--edit--
For additional clarification, I'm just trying to deploy an SSIS package to a server. This used to work in PS5, but isn't any longer.
With this command: [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Management.IntegrationServices")

In Powershell 5, I get this:

GAC Version Location

True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SQLServer.Management.IntegrationServices\13.0.0.0__8984...

In Powershell 7, I get this:
MethodInvocationException: Exception calling "LoadWithPartialName" with "1" argument(s): "Could not load file or assembly 'Microsoft.SQLServer.Management.IntegrationServices, Culture=neutral, PublicKeyToken=null'. Operation is not supported. (0x80131515)"

How do I get PS7 to run things correctly? Do I have to install a different version of SSIS tools?

Answer 1

Rich Matheisen 36,481

Version 14.0.0.0 is part of SQL Server 2017. Is that what you have installed on your machine?

SQL Server Version Assembly Version Number
SQL Server 2005 9.0.0.0
SQL Server 2008 10.0.0.0
SQL Server 2008 R2 10.0.0.0
SQL Server 2012 11.0.0.0
SQL Server 2014 12.0.0.0
SQL Server 2016 13.0.0.0
SQL Server 2017 14.0.0.0

Oblio 1 Reputation point

2022-02-08T23:25:19.99+00:00

Do I have to have SQL Server installed on my machine to deploy the code to another SQL Server?
Rich Matheisen 36,481 Reputation points

2022-02-08T23:46:48.427+00:00

Do you have the correct VERSION of SQL Server installed on the machine that's running the code that fails?

Remember, you need version 14.0.0.0 of Microsoft.SQLServer.Management.IntegrationServices to satisfy the requirement in the [System.Reflection.Assembly]::Load() parameters.

Answer 2

Oblio 1

Right, so, again: I have to have all of SQL Server installed on my workstation in order to deploy an SSIS package to the SQL Server that will be running it?

Like, imagine I check out a solution from version control with the package I want. I then want to build and deploy it to an instance of SQL. Are you saying I can't do that unless I ALSO have SQL installed on my workstation?

Rich Matheisen 36,481 Reputation points

2022-02-09T02:52:32.2+00:00

Probably not SQL Server, but I'd guess that you'd need the SQL Client software installed.

You only have a windows-server-powershell tag on your original post. I'm only telling you why you got the error from PowerShell. Someone more familiar with the SQL product would probably be able to answer your SQL product question about the packaging of the different version of that product.
Oblio 1 Reputation point

2022-02-09T19:44:42.793+00:00

No, I understood what the error was saying and what you were trying to find out. However, what I want to know is: when I follow the instructions on the tutorial, it doesn't work. I assume that there is a prerequisite that the authors assume users have already performed, but I don't know what that is. There are no instructions for what has to be installed on the machine before you can make use of these deployment steps. How do I make this work?
Rich Matheisen 36,481 Reputation points

2022-02-09T20:29:09.787+00:00

The people that answer (or try to answer) the question here are all volunteers. We're not affiliated with Microsoft. Occasionally someone from MS will answer a question, but it's not something you can count on.

The DLL you need is part of the SQL Client and SQL server products. You'll probably be able to use the SQL Server 2019 Express download (sql-server-downloads) and install the client stuff from the download.

I can't tell you where to look for the prerequisites for using SQL integration services, though.

Answer 3

Limitless Technology 37,651

Hello @Oblio

There is a known issue in SQL Server 2016, for the error 0x80131515 on SSIS. If that is your version, please check the next KB article:

https://support.microsoft.com/en-us/topic/kb4134601-fix-could-not-load-file-or-assembly-microsoft-analysisservices-adomdclientui-error-when-a-process-full-operation-is-run-in-sql-server-55ccd19d-24e6-7255-cd16-3a96aabe9418

Hope this helps with your query,

--
--If the reply is helpful, please Upvote and Accept as answer--

Oblio 1 Reputation point

2022-02-14T16:01:29.007+00:00

Thanks, but to be clear, I'm not running this on the server. The errors are occurring on my workstation when I attempt to deploy to the server.
Rich Matheisen 36,481 Reputation points

2022-02-14T16:20:47.72+00:00

Have you installed the SQL 2017 client software on your workstation yet?
Oblio 1 Reputation point

2022-02-14T17:25:58.88+00:00

Yes? No? I don't know? I have the latest SSMS, I have SQL Developer, I have SSDT from VS2019, and I've tried "Install-Module -Name SqlServer -Force". I think, ultimately, the later is the way I should be going because I want to use a powershell script to run the deploy. I just don't know how to get PS7 to find/load the correct assemblies.
Rich Matheisen 36,481 Reputation points

2022-02-14T19:48:37.67+00:00

In your original post you said:

"I get this error: MethodInvocationException: Exception calling "Load" with "1" argument(s): "Could not load file or assembly 'Microsoft.SQLServer.Management.IntegrationServices, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL'

You also said (and this is where I missed something important):

"In Powershell 5, I get this:

GAC Version Location

True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SQLServer.Management.IntegrationServices\13.0.0.0__8984..."

Note that you have on your machine, version 13.0.0.0. However, the code you're running explicitly says if requires version 14.0.0.0.

So, alter the version number in your code to agree with the version you have.
Oblio 1 Reputation point

2022-02-14T20:05:52.59+00:00

Ok, so, I'm trying not to get too hung up on the version number thing here. Yes, I know the numbers are different. The code on the MS site has the version and the public key token specified.

However, just so we can get past this point, here is the result from PS5:

===== [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL")

GAC Version Location

True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\13.0.0.0__8984...

And, here is the result from PS7:

===== [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL") MethodInvocationException: Exception calling "Load" with "1" argument(s): "Could not load file or assembly 'Microsoft.SQLServer.Management.IntegrationServices, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL'. Operation is not supported. (0x80131515)"
Rich Matheisen 36,481 Reputation points

2022-02-14T21:02:54.653+00:00

Which version of the SqlServer module did you install?
HAve you tried the pre-release version of the module?
From the release notes in the "package details" section of the module's home page:

Notes:
* The AS cmdlets on PS7 are still in Preview. If you do not see it in this release, make sure
you install the latest preview version of the module on PS7, by passing the -AllowPrerelease
parameter to the Install-Module cmdlet.

Did you have the older version of this module (SQLPS) installed? I think I read somewhere that there are (or may be) problems when both are installed.

SSIS deployment through powershell 7

GAC Version Location

3 answers

GAC Version Location

True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\13.0.0.0__8984...

Activity