1. Create a new class library in C# and replace the default Class1.cs by the code belowusing System;
    using System.Collections.Generic;
    using System.Text;namespace callssis

    { public class Class1{

    public string test()

    {return “I am external assembly”;}}}

  2.  

    We will call this dll’s test function in SSIS

     

    1. Set the framework as 2.0 

       

    2. Sign it with a strong name 

       

    3. Open Visual studio command prompt and register this assembly in GAC 

      C:\Windows\system32>gacutil /i “C:\Users\ar11942\documents\visual studio 2010\Pr

       

      ojects\callssis\callssis\bin\Debug\callssis.dll”

       

      Output

       

      Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.1

       

      Copyright (c) Microsoft Corporation. All rights reserved.

       

      Assembly successfully added to the cache

       

    4. Create a new SSIS project . 

      1. Add a user variable foo and set it to “Before Call” 

         

      2. Create a script task and add this variable as Read/Write 

         

      3. Add reference to the callssis dll 

         

      4. Paste the following code in the script 

        using callssis;

         

        //and Main is

         

      public

      void Main()

       

      {

       

       

      // TODO: Add your code here

       

      Dts.TaskResult = (int)ScriptResults.Success;

       

       

      MessageBox.Show(new System.IO.FileInfo(@”C:\Users\ar11942\Documents\visual studio 2010\Projects\callssis\callssis\bin\Debug\callssis.dll”).Exists.ToString());

       

       

      //var assembly = Assembly.Load(@”C:\Users\ar11942\Documents\visual studio 2010\Projects\callssis\callssis\bin\Debug\callssis.dll”);

       

       

      //var myType = assembly.GetType(“Class1″);

       

       

      //var myMethod = myType.GetMethod(“test”);

       

      callssis.Class1 foo = new

      Class1();

       

       

      //object obj = Activator.CreateInstance(myType);

       

       

      //Dts.Variables["foo"].Value = myMethod.Invoke(obj, null);

       

      Dts.Variables["foo"].Value = foo.test();

       

       

      MessageBox.Show(Dts.Variables["foo"].Value.ToString());

       

       

      }            

       

      1. Make sure the ssis script task’s properties are same as you dll (framework 2.0 in my case for both DLL and script task project ) Copy the external dll to C:\Program Files\Microsoft SQL Server\100\DTS\binn      or C:\Windows\Microsoft.NET\Framework\v2.0.50727\

         

         

      2. Run the package. If you see this , the call has worked  

        NOTE:-

         

        I was getting this exception at one point .

         

        SSIS package “Package.dtsx” starting.

         

        Error: 0×1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.IO.FileNotFoundException: Could not load file or assembly ‘callssis, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4′ or one of its dependencies. The system cannot find the file specified.

         

        File name: ‘callssis, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4′

         

        at ST_a10608aff1784157b38ecee961f5cfbc.csproj.ScriptMain.Main()

         

        WRN: Assembly binding logging is turned OFF.

         

        To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.

         

        Note: There is some performance penalty associated with assembly bind failure logging.

         

        To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

         

        — End of inner exception stack trace —

         

        at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

         

        at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

         

        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()

         

        Task failed: Script Task

         

        Warning: 0×80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

         

        SSIS package “Package.dtsx” finished: Failure.

         

        If you get this then maybe your dll is not registered in GAC properly. Here are the steps I took

         

        1. Uninstall the assembly from GAC 
        2. Got to Visual Studio à SDK tools àFusion Log Viewer and run it . This can give you more description of assembly binding errors. 
        3. In order to make the FusionLogViewer work you need this in you registry so go to 

          HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion and create these

           

           

        4. In my case I pointed it to C:\temp\fusion but you can point it wherever you want. Then you can launch the FusionLogViewer.exe from the SKD tools I mentioned above and it will show you the errors. Here was mine 

        The operation failed.

         

        Bind result: hr = 0×80070002. The system cannot find the file specified.

         

        Assembly manager loaded from: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\mscorwks.dll

         

        Running under executable C:\Program Files\Microsoft SQL Server\100\DTS\binn\DtsDebugHost.exe

         

        — A detailed error log follows.

         

        === Pre-bind state information ===

         

        LOG: User = EUR\ar11942

         

        LOG: DisplayName = callssis, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4

         

        (Fully-specified)

         

        LOG: Appbase = file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/

         

        LOG: Initial PrivatePath = NULL

         

        LOG: Dynamic Base = NULL

         

        LOG: Cache Base = NULL

         

        LOG: AppName = NULL

         

        Calling assembly : (Unknown).

         

        ===

         

        LOG: This bind starts in default load context.

         

        LOG: Using application configuration file: C:\Program Files\Microsoft SQL Server\100\DTS\binn\DtsDebugHost.exe.Config

         

        LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v2.0.50727\config\machine.config.

         

        LOG: Post-policy reference: callssis, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4

         

        LOG: GAC Lookup was unsuccessful.

         

        LOG: Attempting download of new URL file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/callssis.DLL.

         

        LOG: Attempting download of new URL file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/callssis/callssis.DLL.

         

        LOG: Attempting download of new URL file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/callssis.EXE.

         

        LOG: Attempting download of new URL file:///C:/Program Files/Microsoft SQL Server/100/DTS/binn/callssis/callssis.EXE.

         

        LOG: All probing URLs attempted and failed.

         

         

    This is really useful when you are debugging external assembly bindings in any .Net project and after this I reinstalled the DLL and it worked.

     

    So hopefully you know

     

    1. How to call external (custom) assembly in SSIS 
    2. How to use FusionLogView.exe a great tool
About these ads