Could not load file or assembly SSIS Script Task

Question

I have a DTSx with a Script Task that will replace some labels in a Word Document and then save it as PDF but it constantly shows error : "Could not load file or assembly '' or one of its dependencies. The system cannot find the file specified." I've tried using Aspose, GemBox, Syncfusion and the error is always the same. When I use them from NuGet the Task doesn't even start...but when I copy the DLLs and all the attached files to a folder in my computer it shows the error above. I use framework 4.

Can you please help?

Thanks.

In script task did you add the reference? and add proper using statement? — Brad, Jan 08 '21 at 16:27
yes. I add the reference. From NuGet (didn't work) and then delete it and add it through browse in my pc. Also added the using — Filipe Vilhena, Jan 08 '21 at 16:28
Is it a managed dll? It may be a c++ dll which will not load into a c# managed application. — jdweng, Jan 08 '21 at 16:33
I used the dll's i described above. I donwloaded them from NuGet: syncfusion, Gembox, Aspose.. — Filipe Vilhena, Jan 08 '21 at 16:38

billinkc · Answer 1 · 2022-08-08T15:09:00.377

nuget packages and SSIS script tasks don't work. Yes, it will compile for you but when you X out of the Script Editor, the nuget packages don't get serialized in with the executable and when it runs, there's no "brains" there to download the expected assemblies. SSIS expects the assemblies to be either serialized into what gets stored in the SSIS package or to be in the Global Assembly Cache (GAC).

How do I get a nuget package installed to the GAC?

Three big steps

Find GACUTIL.exe

Open Command Prompt cd "\Program Files (x86)" and then dir /s /b gacutil.exe That will tell you where the gacutil.exe program lives (if it exists on your machine - it might not).

As a reference, here's what my development machine looks like

C:\Program Files (x86)>dir /s /b gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\x64\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.7.2 Tools\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.7.2 Tools\x64\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8 Tools\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8 Tools\x64\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\gacutil.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\x64\gacutil.exe

Make note of where one of those executables lives. As there are spaces in the path, you will need to wrap the path with double quotes to run it, i.e. "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe"

Find the assembly

Find the nuget package you need and pray that it's a signed assembly. Script Task/Component development in SSIS is a weird beast. My SSIS package is in this path \users\bfellows\Documents\Solution\Project\MyPackage.dtsx

When I edit a script task though, what happens? A new instance of VS spins up and I do the things I need to do. Say I add newtonsoft to my package so I json all the things.

Wait, what's the gibberish? My Project Folder is pointed at C:\Users\bfellows\AppData\Local\Temp\Vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\VstacRTUuMELWk2UL3Wn4JQCWw\ Yes, the task/component editor uses temporary path to create a new solution and project, does all its work there and when you close out the editor, all of the project solution bits get serialized back into the SSIS package. nuget stuff is not included in the serialized bits. Ooops.

So, you need the assembly but it's in a temporary path - what do you do? You edit the package, ensure your nuget package is currently downloaded and do not close the script task. Right Click on the solution name (VstaProjects) and choose Properties.

Find that Path and copy the resulting value out. My solution file is located at C:\Users\bfellows\AppData\Local\Temp\Vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\VstaProjects.sln

That's great, it's one folder above the Project Folder but we won't need the VstaProject.sln part of the path.

Open a command prompt and change directory to that location.

cd C:\Users\bfellows\AppData\Local\Temp\Vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A

From here, you have a package subfolder. The command dir /s /b packages*.dll will find all the assemblies (files with a .dll extension) located under the packages folder, recursively, and provide a brief set of information - in this case, the fully qualified path.

C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A>dir /s /b packages\*.dll
C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\packages\Newtonsoft.Json.13.0.1\lib\net20\Newtonsoft.Json.dll
C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\packages\Newtonsoft.Json.13.0.1\lib\net35\Newtonsoft.Json.dll
C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\packages\Newtonsoft.Json.13.0.1\lib\net40\Newtonsoft.Json.dll
C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\packages\Newtonsoft.Json.13.0.1\lib\net45\Newtonsoft.Json.dll
C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\packages\Newtonsoft.Json.13.0.1\lib\netstandard1.0\Newtonsoft.Json.dll
C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\packages\Newtonsoft.Json.13.0.1\lib\netstandard1.3\Newtonsoft.Json.dll
C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\packages\Newtonsoft.Json.13.0.1\lib\netstandard2.0\Newtonsoft.Json.dll

Goodness, that's a lot of assemblies. Which one is right for me? SSIS Script Tasks/Components are built against the 4.x Framework of the .NET language. The highest net branch listed is net45 so that's what we need to know to actually install the assembly.

packages\Newtonsoft.Json.13.0.1\lib\net45\Newtonsoft.Json.dll

Install the assembly

Open a command prompt in administrator mode. This may prompt the UAC dialog to pop and you might need to use admin credentials depending on your security set up.

Installation to the GAC is straight-forward after that.

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe" /if C:\path\to\my\nuget\package.dll

/if signals Install and Force a re-install even if the assembly already exists.

If I wanted to install the newtonsoft dll from above, that syntax would be

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe" /if "C:\Users\bfellows\AppData\Local\Temp\vsta\SSIS_ST140\VstatIBdQdlmHEa__RFeT36Nd9A\packages\Newtonsoft.Json.13.0.1\lib\net45\Newtonsoft.Json.dll"

But I got an assembly from someone else

Fear not, you can handle this

Apparently, you can sign an assembly (aka add a strong name to an existing DLL) if it wasn't already signed h/t to @kearl

Open Visual Studio Command Prompt
Generate a KeyFile sn -k keyPair.snk
Get the MSIL for the assembly ildasm SomeAssembly.dll /out:SomeAssembly.il
Rename the original assembly, just in case ren SomeAssembly.dll SomeAssembly.dll.orig
Build a new assembly from the MSIL output and your KeyFile ilasm SomeAssembly.il /dll /key=keyPair.snk

thanks billinkc, would you have the steps to get the DLL working with the SSIS package? — theITvideos, Jan 02 '22 at 05:05
@theITvideos Take a peek at the updated answer for a verbose explanation and let me know if you're still struggling — billinkc, Jan 02 '22 at 18:12
Thank you for the detailed explanation billinkc. I'm assuming I would have to do this on the actual SQL Server box? — theITvideos, Jan 04 '22 at 04:00
@theITvideos Any server that you might need to run the package on as well as any new developer that needs to maintain the work. — billinkc, Jan 04 '22 at 14:52
Useful link on how to sign assembly if you don't have a signed assembly: http://codeingaddiction.blogspot.com/2011/06/how-to-add-strong-name-to-existing-dll_16.html — Kearl, Aug 02 '22 at 03:24

score 0 · Answer 2 · answered Nov 17 '22 at 13:29

I found a similar issue when working with sftp and the below steps helped me out and resolved in vs2017 community with SSIS package.

Open PowerShell and run as administrator.
Download the respective .dll file from Nuget package and copy the below commands in PowerShell.
[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a") $publish = New-Object System.EnterpriseServices.Internal.Publish $publish.GacInstall("C:\Users\hp\Downloads\renci.sshnet\renci.sshnet.dll");
Run the above command by replacing the downloaded .dll filepath. Press enter
Gac turns true.Run script task in SSIS.