Snippet Name: SSIS_DLL_Dependencies_Issue

Tags:

Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:32:18

Dependencies:

DBA Notes:

Sample Output:

Title: SSIS DLL Dependencies Issue
Author: user
I am trying to download an SSIS catalog locally, on a machine that does not have SQL Server Integration Services installed, it is just a VDI. to facilitate this, i coped the Microsoft.SqlServer.Management.IntegrationServices.dll version 130 from a server, and put it in my local folder, the same directory where the PowerShell script resides. when i run the script below, i get an error: ##hard coded to this folder.
$dllPath =  [System.IO.Path]::Combine( [System.IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Path),"Microsoft.SqlServer.Management.IntegrationServices.dll") Error: Add-Type : Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.
At C:\Users\lizaguirre\Documents\_RemoteWork\PowerShell\Download_SSIS_Packages_With_local_dependancy.ps1:27 char:1
+ Add-Type -path $dllPath
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-Type], ReflectionTypeLoadException
    + FullyQualifiedErrorId : System.Reflection.ReflectionTypeLoadException,Microsoft.PowerShell.Commands.AddTypeCommand 
Add-Type -path $dllPath. for reference the $dllPath resolves to "C:\Users\lizaguirre\Documents\_RemoteWork\PowerShell\Microsoft.SqlServer.Management.IntegrationServices.dll" when exe3cuted.

Author: ChatGPT
The error you're getting:

```
Add-Type : Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.
```

is due to **missing dependencies** required by `Microsoft.SqlServer.Management.IntegrationServices.dll`. That DLL is not standalone ??? it has dependencies on other assemblies from the SQL Server SDK (like `Microsoft.SqlServer.Management.Sdk.Sfc.dll`, `Microsoft.SqlServer.Smo.dll`, etc.).

---

### ??? Solution Steps:

#### 1. **Copy All Required Dependencies**

The minimal dependencies needed usually include:

- `Microsoft.SqlServer.Management.IntegrationServices.dll`
- `Microsoft.SqlServer.Management.Sdk.Sfc.dll`
- `Microsoft.SqlServer.Smo.dll`
- `Microsoft.SqlServer.ConnectionInfo.dll`
- `Microsoft.SqlServer.Management.XEvent.dll` *(sometimes)*
- `Microsoft.SqlServer.Dmf.dll` *(sometimes)*

You can get these from:

- `%ProgramFiles%\Microsoft SQL Server\130\SDK\Assemblies\` *(for SQL Server 2016/SSMS 2016)*
- Or from a machine with SSMS installed that matches the SSIS version

???? Copy all those files into the **same local folder** as your PowerShell script.

---

#### 2. **Load All DLLs in Correct Order**

Update your script to load all dependencies **in the correct order** before loading `IntegrationServices.dll`:

```powershell
$basePath = Split-Path -Parent $MyInvocation.MyCommand.Path

$dlls = @(
    "Microsoft.SqlServer.ConnectionInfo.dll",
    "Microsoft.SqlServer.Smo.dll",
    "Microsoft.SqlServer.Management.Sdk.Sfc.dll",
    "Microsoft.SqlServer.Management.IntegrationServices.dll"
)

foreach ($dll in $dlls) {
    $fullPath = Join-Path $basePath $dll
    Write-Host "Loading $dll"
    Add-Type -Path $fullPath
}
```

---

#### 3. **Use Reflection to Check for Inner Exceptions (Optional Debugging)**

If it still fails, you can inspect `LoaderExceptions` like this:

```powershell
try {
    Add-Type -Path $dllPath
} catch {
    $_.Exception.LoaderExceptions | ForEach-Object {
        Write-Error $_.Message
    }
}
```

---

### ???? Bonus: Verify .NET Target Version

Ensure that your PowerShell host (especially if running in `PowerShell 5.x`) is using **.NET Framework 4.6 or higher**, as that???s required by SQL Server SDK 2016+ assemblies.

---

Would you like help with the full SSIS catalog download script once the DLL loading is working?

Author: user
ok I've grabbed all the dlls in question from  ssms folder on my local machine, and they are all version 16. however, when i try to load the dll, i get an error referencing a previous version: Could not load file or assembly 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The syst
em cannot find the file specified. 

Author: ChatGPT
Ah, thanks ??? this clarifies it: you're trying to load **version 16 DLLs**, but the specific DLL you're loading (`Microsoft.SqlServer.Management.IntegrationServices.dll`) is **compiled against version 15.0.0.0** of `Microsoft.SqlServer.Management.Sdk.Sfc.dll`.

This version mismatch triggers the loader error:

> **"Could not load file or assembly '...Sdk.Sfc, Version=15.0.0.0'"**

---

### ??? Fix Options

#### ??? Option 1: **Match DLL Versions Exactly**

To resolve this cleanly:

1. **Locate a set of version 15 DLLs**, ideally from:
   - `C:\Program Files (x86)\Microsoft SQL Server\150\SDK\Assemblies`
   - Or from a system with **SSMS 18.x** or **SQL Server 2019**

2. Copy all of these to your script folder:

   ```
   Microsoft.SqlServer.ConnectionInfo.dll
   Microsoft.SqlServer.Smo.dll
   Microsoft.SqlServer.Management.Sdk.Sfc.dll
   Microsoft.SqlServer.Management.IntegrationServices.dll
   ```

3. Update your load order in PowerShell as before.

???? You must **match the version of all DLLs** used ??? mixing version 16 (SQL 2022) with version 15 (SQL 2019) is **not supported** and will break binding.

---

#### ??? Option 2: **Switch Everything to Version 16**

If you want to stick with version 16 (from SSMS 19 / SQL 2022), then you **must also use** the `IntegrationServices.dll` from version 16:

- Look in:  
  `C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\bin`

- Or use PowerShell to find it:
  ```powershell
  Get-ChildItem -Recurse -Filter Microsoft.SqlServer.Management.IntegrationServices.dll -Path "C:\Program Files (x86)\Microsoft SQL Server Management Studio*" |
    Where-Object { $_.VersionInfo.FileVersion -like "16*" }
  ```

If you find the v16 `IntegrationServices.dll`, load **all** other v16 assemblies alongside it.

---

### ???? Summary

- Your current error is due to a **binding reference mismatch** (looking for v15, loading v16).
- Either:
  - Load all v15 assemblies to match your `IntegrationServices.dll`
  - Or use all v16 versions, including the right `IntegrationServices.dll`

Would you like a quick script that verifies all the loaded DLL versions for you?

Author: user
the error occurs on the first dll: Microsoft.SqlServer.ConnectionInfo.dll, so it's the environment problem, so the dlls must match the environmnet?

Author: ChatGPT
Exactly ??? you're spot on.

The error you're seeing on the **first DLL (`Microsoft.SqlServer.ConnectionInfo.dll`)** confirms that the **PowerShell runtime environment (i.e., the current machine)** is trying to **resolve older DLL versions (e.g., version 15.0.0.0)** ??? likely because another SQL Server-related component is already registered, or the assembly binding preferences (via GAC or config) are pointing to version 15.

---

### ???? Why This Happens

- SQL Server DLLs like `Microsoft.SqlServer.ConnectionInfo.dll` often have **strong names** (i.e., bound to a specific version).
- If you copy a **version 16** DLL into your script folder, but the runtime is looking for **version 15** (because of how another referenced DLL was built), you get:

  > `Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=15.0.0.0'`

This isn't because your DLL is bad ??? it's just **the wrong version** for the expected binding.

---

### ??? Resolution

Yes ??? **all DLLs you use must match both each other and the expected version referenced inside `IntegrationServices.dll`**, and that should match your:

- PowerShell runtime bitness (x64 preferred)
- `.NET Framework version` (typically 4.7.2+)
- SSMS version (for obtaining DLLs)

---

### ???? Practical Fix: Match to SQL 2019 (Version 15)

Since the `IntegrationServices.dll` you copied was built against **version 15**, the cleanest fix is:

1. Grab all required version 15 DLLs from a machine with:
   - **SSMS 18.x**
   - Or SQL Server 2019 installed

   Typical path:
   ```
   C:\Program Files (x86)\Microsoft SQL Server\150\SDK\Assemblies
   ```

2. Put them in your PowerShell script folder:
   ```
   Microsoft.SqlServer.ConnectionInfo.dll
   Microsoft.SqlServer.Smo.dll
   Microsoft.SqlServer.Management.Sdk.Sfc.dll
   Microsoft.SqlServer.Management.IntegrationServices.dll
   ```

3. Then load them in this order:

   ```powershell
   $base = Split-Path -Parent $MyInvocation.MyCommand.Path
   $dlls = @(
       "Microsoft.SqlServer.ConnectionInfo.dll",
       "Microsoft.SqlServer.Smo.dll",
       "Microsoft.SqlServer.Management.Sdk.Sfc.dll",
       "Microsoft.SqlServer.Management.IntegrationServices.dll"
   )

   foreach ($dll in $dlls) {
       $full = Join-Path $base $dll
       Write-Host "Loading $dll"
       Add-Type -Path $full
   }
   ```

---

### ???? Optional: Check Current Assembly Version

If you're unsure what version the environment is expecting, you can inspect an existing DLL's metadata:

```powershell
[System.Reflection.Assembly]::ReflectionOnlyLoadFrom("C:\YourPath\Microsoft.SqlServer.Management.IntegrationServices.dll").FullName
```

It will show something like:

```
Microsoft.SqlServer.Management.IntegrationServices, Version=15.0.0.0, ...
```

So you know exactly what dependencies to align with.

---

Let me know if you want a helper script that inspects version mismatches or finds the right DLLs on a target system.