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”;}}}


    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






      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



      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




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



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


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








      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  



        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=, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4′ or one of its dependencies. The system cannot find the file specified.


        File name: ‘callssis, Version=, 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=, Culture=neutral, PublicKeyToken=c6c260eaf518a6c4




        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=, 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