So you've created an awesome class library you want to use in an SSIS package. Instead of worrying about registering it in the GAC, you can do the following to make sure your code will work in production.
Class Library Setup
- Target Framework should be .NET Framework 4 for SSIS Packages used in SQL 2012.
- The Assembly should be signed with a strong key.
Deploying the Assembly
- After your class library is built and ready to go, copy it to the following locations:
- C:\Program Files\Microsoft SQL Server\110\DTS\Binn
- C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn
- This will ensure the assembly is available whether the package is run in 32 or 64 bit mode
- If your assembly relies on any other assemblies that are not registered in the GAC, copy them to the aforementioned folders as well.
Referencing the Assembly in a Script Task
- In the Script Editor (ScriptMain.cs), the assembly can be referenced just like any other assembly
- In the Solution Explorer Window, right click on References and select "Add Reference..."
- You will probably have to Browse for the dll, you can browse to any instance of the current version of the dll. When the package executes it will use the dll in the "DTS\Binn" folder
- The custom assembly can now be used right in your script task
June 8, 2021 - Update
I'm unsure with what version is started happening, but when developing for SQL Server 2017, the above solution failed to work. I began receiving invocation errors, and dll is not found even after placing the dll in the above folders.
The solution I found was to add a ResolveEventHandler. Here are some example Stack Overflow questions:
Here's how it looks in one of my packages that looks for specific emails with attachments and downloads the attachments. The relevant code is highlighted. Basically, this intercepts the resolver process and says, if the reference is like "abc" then load the dll located at "...\abc.dll".
best advice...
ReplyDeleteI had to use custom DLL and had faced the same issue in SSIS, so I tried placing in above mentioned folder but it did not resolve. The exception showed the folder where it was tryng to find the dll C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\CommonExtensions\Microsoft\SSIS\140\Binn
ReplyDeleteAfter placing the dll's there, the refernce issue was resolved but my doubt is this folder was prompted in exception while doing local testing, while doing deployment to different server , how will we know where to place the dll if that server doesn't have visual studio installed
Hi Spoorthi, I've added an update to the post. Since I created the original post, I've had to take additional steps to work with the SQL Server 2017. I hope it helps you out!
Delete