Last I found several SSIS packages with the same code in different script tasks. Because the code was duplicated it was hard to maintain. That’s why we created a C# library and used the library in the script tasks.
C# Library
First, we need to create a Class Library project in Visual Studio. So open Visual Studio and add a new project. Choose for Visual C# and then select a Class Library project.
For this demonstration a made a simple class that returns a string value.
namespace ClassLibrary
{
public class MyClass
{
public string getString()
{
return "Test";
}
}
}
Last thing we need to do is sign the assembly. Go to the properties of the project and open tab Signing. Then enable sign the assembly and add a new key file.
Don’t forget to (re)build your project.
C# Library Installation
To be able to use your assembly in the SSIS script task you need to copy the dll in the bin folder of the SQL server assembly folder. The path to this folder may vary depending on the version of SQL Server installed. In my case it is “C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies”.
Next, we need to register the assembly to GAC, run this command in Command Prompt as administrator:
“gacutil.exe” -i “C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\ClassLibrary.dll”
Caution!
When your library uses other libraries, for instance Newtonsoft.Json. You also need to register this library (steps above).
Library in SSIS script task
To use the C# library in a SSIS script tasks we need to add it to the references
Select the library in “C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies”
Now you can use your library like any other. And all your code is in your library.