#Extracting SSIS packages via PowerShell

32 messages · Page 1 of 1 (latest)

zenith estuary
#

Hi all, I am trying to extract .ispac (which is a compressed xml which I will extract metadata from) files from a SQL server, but I'm having some issues with getting the required Assemblies to connect to the SQL server.

The current error I am getting is:
"Cannot find type [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]: verify that the assembly containing this type is loaded. InvalidOperation". This occures when I try to create New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($serverConnection)

I'm new to powershell and I'm not convinced I'm loading the correct .dlls, or that they are compatible. As can be seen below I've added Microsoft.SqlServer.Management.IntegrationServices.dll manually, but the error is indicating I haven't added it at all.

What am I doing wrong? Is there a better way to do this?

I've spent countless hours on this, and need some help. Please see my code below.

# 
# Define SSIS Server and Folder
$serverName = "ssdb000"  
$ssisCatalogName = "SSISDB"
$folderName = "YourSSISFolder"  
$outputFolder = "C:\SSIS_Projects"


# ----------------- Load IntegrationServices Manually----------------
#code in this block is paraphrased, checks that they exist are executed, all .dlls do exist.


$integrationAssemblyPath = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn\\Microsoft.SqlServer.Management.IntegrationServices.dll"
Add-Type -Path $integrationAssemblyPath #used to load a .NET assembly into the PowerShell session.


$sTraceAssemblyPath = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Microsoft.SqlServer.Diagnostics.STrace.dll"
Add-Type -Path $sTraceAssemblyPath #used to load a .NET assembly into the PowerShell session.


$ConnectionInfoAssemblyPath = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\microsoft.sqlserver.sqlmanagementobjects.172.64.0\lib\net8.0\Microsoft.SqlServer.ConnectionInfo.dll"
Add-Type -Path $ConnectionInfoAssemblyPath #used to load a .NET assembly into the PowerShell session.


$sqlClientAssemblyPath = "C:\Users\XXXXX\Documents\PowerShell\Modules\SqlServer\22.3.0\coreclr\runtimes\unix\lib\net6.0\Microsoft.Data.SqlClient.dll"
Add-Type -Path $sqlClientAssemblyPath #used to load a .NET assembly into the PowerShell session.

#------------------------------------------------------------

 Connect to SQL Server
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$serverConnection.ServerInstance = $serverName
$serverConnection.LoginSecure = $true  # Use Windows Authentication

# Create SSIS connection object
$ssisServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($serverConnection)
zenith estuary
#

I think the real issue is getting the correct assemblies to work together rather than any actual problem with the code...

slate sparrow
#

nvm i guess im not awake, i clearly cant read

#

you are just trying to connect to then run a query for the stored procedure to actually extract it, right?

dusky zephyr
#

Yeah SSIS is a real pain with the dlls. There is a specific dll for each version of SSIS. You can't just use the one built into SSMS as it is locked to one version of SSIS I believe. I would just query the metadata in SSISDB. What exactly are you looking for in the ispac?

#

Yeah so the thing to take from the above is that SSIS is pain to work with on dotnet. I get

MethodInvocationException: Exception calling ".ctor" with "1" argument(s): "Could not load file or assembly 'Microsoft.SqlServer.Diagnostics.STrace, Version=16.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The system cannot find the file specified."

when I try to new()-up an IntegrationServices object

#

That is on v7.

#

I get the below on v5.1

add-type : Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.
At line:1 char:1
+ add-type -Path 'C:\Program Files (x86)\Microsoft SQL Server Managemen ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-Type], ReflectionTypeLoadException
    + FullyQualifiedErrorId : System.Reflection.ReflectionTypeLoadException,Microsoft.PowerShell.Commands.AddTypeCommand

I'm also running SSMS v20.

#

So [reflection.assembly]::Load() gets me a bit further but it still doesn't work.

zenith estuary
zenith estuary
# dusky zephyr Yeah SSIS is a real pain with the `dll`s. There is a specific `dll` for each ver...

Yeah, I've easily spent 6 hours on this one trying to debug it and get those dlls to work nicely together. I really do appreciate the response and you trying to work it out!

I want to download the .ispacs so I can extract metadata from it in an automated way, as we have so many SSIS Packages on our servers it would be a pain to do manually and to continue to manage into the future. open to any other suggestions on how to do this

zenith estuary
#

some things I'm pondering is that my ssms version is 15, my smo version is 16, and I am also wondering if I should be using versions that match the actual server, not my specific computers ssms... so confusing

#

I've also created this debugging script, which is attached (too big to paste apparently). I wanted to try and make the versions match, but even after loading the correct versions (or what I assumed to be the correct versions) they still wouldn't play nicely and load. I've added comments below the commands to what the output was. sqlMGMTobjs15 is the v150 sql management objects assemblies I downloaded from nuget.

📎 message.txt
zenith estuary
#

Alternatively I could get the users to make a copy of any deployed ssis packages to a folder and just extract metadata from there. Feel like I could spent 20 hours on this and not get it working.

dusky zephyr
#

Are you running v5.1 or v7?

zenith estuary
# dusky zephyr Are you running v5.1 or v7?

I'm not sure which versions youre referring to,

but I have this info:

IntegrationServices version: 15.100.0.0
STrace version: 15.100.0.0
ConnectionInfo version: 17.100.0.0
SqlClient version: 5.0.0.0

SMSS:
SQL Server Management Studio 15.0.18424.0
SQL Server Management Objects (SMO) 16.XX

dusky zephyr
#

5.1 is what is known as Windows Powershell and then the modern version 7 which is known as just PowerShell.

#

Different versions of dotnet

dusky zephyr
#

See I think it is something to do with system.data.sqlclient versus the Microsoft library where ssis library expects the older one.

dusky zephyr
zenith estuary
#

all good if you don't want to spend any time on this one. It really seems like a difficult issue to resolve.
Tomorrow Ill set up a repo of deployed ssis projects and get users to keep that up to date instead, shouldn't take them much effort to do that. as long as they remember.

#

which ill extract metadata from

#

thank you for the help so far!

dusky zephyr
#

as long as they don't encrypt the whole package; otherwise, you would have to figure out the decryption.

#

I assume you are talking about pulling the data from the xml of the .dtsx file?