Many a times in the SSIS Script Task one wants to call a .Net assembly to reuse/leverage the existing functionality.
Now, any .NET assembly you want to refer from an SSIS script task must be in GAC.
In order to access the assembly one has to add a reference to the assembly from SSIS Script Task IDE. But for your assembly to appear in the Add Reference Window it has to be copied under .Net Framework Folder i.e. 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727' Folder, it’s just not enough that the assembly is installed in GAC.
This particular limitation is because Script Task uses the Visual Studio for Applications (VSA) under the covers.
There is a peculiar limitation of VSA that requires you to place any DLLs that you want to reference into the Microsoft.Net\Framework folder of your machine, it's not enough to GAC it.
So what if I just wanted to GAC the assembly and still be able to access it from Script Task???
Certainly we have to use .Net Reflection but what if you are using 2 or more assemblies in SSIS Script task?? Or Are calling .Net assemblies in more than one script task??
You have to Write the .Net Reflection code to create instance that many number of times.
But wait there's one more way that’s easy and reusable.
What I do is, use a function that uses the Reflection to Load the desired assembly at runtime and create an instance of the required type or class.
What I do is copy paste the following function in my script task
' assembly2Load - i.e. Assembly Name: MyProj.Common
' class2AccessWithinAssembly - e.g: FTPDirectoryLister
' Function for Creating Assembly Instances
Private Function CreateObject(ByVal assembly2Load As String, ByVal class2AccessWithinAssembly As String) As Object
'Define variable of type [Assembly]
Dim targetAssembly As [Assembly] = Nothing
'Load your desired assembly from GAC into defined variable e.g. "MyProj.Common"
targetAssembly = [Assembly].LoadWithPartialName(assembly2Load)
'Define variable which used to hold “Type” object
Dim targetType As Type
'Get specific class from assembly e.g. “FTPDirectoryLister” Class
targetType = targetAssembly.GetType(assembly2Load + "." + class2AccessWithinAssembly)
'Define variable of type Object
Dim reqdObjectInstance As Object
'Get the instance of Class Specified e.g. “FTPDirectoryLister”
reqdObjectInstance = targetType.InvokeMember(class2AccessWithinAssembly, BindingFlags.CreateInstance, Nothing, Nothing, Nothing)
Return reqdObjectInstance
End Function
How do I call this Function? Simple:
Dim objCommonAssembly As Object
'Create Common Services Object to Validate the Generated XML
objCommonAssembly = CreateObject("MyProj.Common", "FTPDirectoryLister")
'Once the desired class instance is ready you can call methods in it
Dim listing As String
listing = objCommonAssembly.GetFTPListing(ftpURL)
Finally,
Since we are using reflection we have to add this line in Imports Section
Imports System.Reflection
Also, we have to set:
Option Strict Off
on the first line of the editor, this is required for our casting in the CreateObject function
That’s it we are done. Now one can use the same function in multiple script tasks just copy paste it.
I use this function a lot as it offers me flexibility an code reuse. Hope it will be useful to you too!!!