SSIS throwing an error in a script task before the first line of code is reached

Question

SSIS throwing an error in a script task before the first line of code is reached. Simialr to How can I reference a dll in the GAC from Visual Studio? I did not write it I am just looking at it to see how it works. Is there a rule of thumb of things to look for. It is using a third party library where the orignal code was referencing from the GAC but I am not sure if I installed this 3rd party library into the GAC correctly. So I have referenced the dll direct in the path it was installed into as a first attempt. Like other posts I have read it works fine if I isolate the code into a console app and run it.

Things I have tried so far that work:

• No reference no code other than return success
• With the local reference, no code other than return success

Things I have tried that do not work before I GAC'd:

• kept the Try Catch but one line of code in the try var session = new Session(); Possible 3rd party library is not same framework version??

Code base: Does a Simple File Transfer Protocol call to send a file to a folder on the destination server. Never gets to the break point if I dont install in the GAC. Message: Exception has been thrown by the target of an invocation. Error: at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Things I have tried that do not work after I GAC'd:

• I navigated to the v8.0A bin folder and installed my library opening cmd as administrator and cd to the assembly folder and ran gacutil /i "C:\Program Files (x86)\WinSCP\WinSCPNet.dll" and it said successfully installed. I then tried both referencing the library with the same path and also the assembly path C:\Windows\Microsoft.Net\assembly\WinSCP\WinSCPNet.dll" using CopyLocal = False and it works ok debugging locally but once I deploy the SSIS solution the same annoying error "... Target of an invocation ...".

Answer 1

If it helps anyone with a similar problem when using a 3rd party library in a SSIS package or similar lessons learnt are:

• SSIS requires that the library must be GAC'd
• I can only speak for the modest .NET developers amongst us as even I was a bit rusty on the basic concept of how to reference a library and using early binding,
  1. Use copy local = False in SSIS
  2. Make sure the version of library your reference is the GAC'd version, and if not found in the main GAC reference it by path and find in c:\Windows\Microsoft.Net... if you cannot find it anywhere else.
  3. Note same version is both product and file version you will need to consider - I have not experimented with every combination as I initially thought if the product version was the same that would be good enough, then I thought it would just know to choose the latest version which is not the case either and I only recall getting it to work if both product and file were the same and choosing as I said the GAC version.
  4. Note when you upgrade or copy from another machine and install a library into the GAC that you did not initially install as part of the main product which you are able to do, you have to be aware the way .NET works is that you can have multiple versions of the same library running concurrently and thus referenced by multiple applications each using their own version so as long as the version you reference exists on the machine you are deploying your SSIS solution (NB: it may even stop working when running it locally in VS after a change due to another default supporting thing it requires in the same directory that just works on the server) to and always read the documentation if any if there are any discrepancies between how and what is installed on the server vs your developer machine vs joe blogs user who does not know anything about the concept of automation (i.e. libraries may not be automatically GAC'd in the product installation phase) if you have to worry about it yourself.
• It is not a bad idea to reference the library in say a console application so you can experiment with the library as you don't need it GAC'd to play around with.
• Never assume the a 3rd party library is written by a technical person, seasoned IT professional or the person writing the documentation is a practised consumer of their own or employees library
• Do not assume the person in your company who purchased it knows everything, has read all the features and common issues
• Make sure you are aware of frequency of updates or keep an one eye open if practical to upgrade
• Make sure the developer consuming the use of the library has the nous to try things if it doesn't work one way try another, read the documentation even if it is at first not straight forward, persevere, and thus get to know what the SDK contains as in classes/methods/properties etc. For e.g. most basic thing I would assume when doing SFTP is it can it handle multiple files and sub directories and file masks

As an aside issue I did try at least 6 or maybe more ways until I got it to work how I wanted it to that is copy multiple files using a file-mask in the specified folder only so excluding sub-folders, where the default behaviour of the only code example in the the documentation I could see at first (there was a forum I discovered later that had similar issues been logged but found it easier to just experiment myself) was it automatically created sub-folders, so a copy of the source layout on the destination even if no file was applicable to transfer from it. Further reading and experimentation their was no simple flag to set, the trick happened to be the choice of the method and the format of the file-mask as opposed to the wrong method and the wrong place to set the file-mask on a parameter object and the wrong setting of the mask.