Accessing Custom .NET Assemblies in SSIS 2008 Script Tasks

If you need to access a custom .NET Assembly from an SSIS Script Task, Microsoft doesn’t make things very easy – but it’s still possible with a little setup. This is a great way to introduce custom data types or some new functionality without having to replicate that code in a new environment.

The Setup

  • Windows 7 64-bit
  • Visual Studio 2008
  • SQL Server 2008 64-bit

The Process

  1. Create a signing key (See also, How to: Create a Public/Private Key Pair)
    1. Open Visual Studio 2008 command Prompt – the regular command prompt will not work
    2. Change to a friendly directory: cd %userprofile%Desktop
    3. Create the key file: sn -k key.snk
  2. Sign the assembly – There are a few ways to do this, but I found this to be the easiest. If you want to sign it some other way, check out How to: Sign an Assembly with a Strong Name
    1. Right-click the Project
    2. Select “Properties”
    3. Navigate to the “Signing” tab
    4. Browse to strong name key file (which was created in the previous step)
    5. Recompile the project
  3. Copy the re-compiled assembly to your GAC
    1. gacutil -i “C:PathtoCustomAssemblyName.dll”
  4. Copy assembly to “%programfiles(x86)%Microsoft SQL Server100SDKAssemblies”
  5. Add reference in script task. Repeat this for every Script Task you want to access this assembly from
    1. Right-click References
    2. Click “Add Reference…”

      Add Reference...
      Add Reference...
    3. On the .NET tab, scroll to find your assembly
    4. Press “OK”
    5. The Assembly should now appear under the References list
  6. Add a reference to the assembly in code, at the top
    1. (C#) Using CustomAssemblyName;
    2. (VB.NET) Imports CustomAssemblyName
  7. You should now have full access to the imported DLL

Caveats

This method works pretty well, but deployment isn’t exactly seamless – you’ll have to repeat this for each server and re-register & copy the DLL separately for any updates. Additionally, there is no way to globally add the assembly reference to the entire project or package. Instead, you’ll have to repeat step 6 (adding the reference) for every Script Task.

6 thoughts on “Accessing Custom .NET Assemblies in SSIS 2008 Script Tasks

  1. Thanks for the detailed article. I am also using similar environment, and was able to sign my dll, add it to gac. The reference was added to the script task in SSIS, and I was able to use the methods I created, and compilation was good. However when executing the script task, it gave me an error that it cannot find the dll. Have you ever encountered this or have any clues what might have gone wrong?

    This is the full error message I received in the execution results:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.IO.FileNotFoundException: Could not load file or assembly ‘TestDll, Version=1.0.4561.28296, Culture=neutral, PublicKeyToken=1356c0781f3ac2b7′ or one of its dependencies. The system cannot find the file specified.
    File name: ‘TestDll, Version=1.0.4561.28296, Culture=neutral, PublicKeyToken=1356c0781f3ac2b7′
    at ST_98a0766ae49c4d888015f8090c9c348a.csproj.ScriptMain.Main()

    WRN: Assembly binding logging is turned OFF.
    To enable assembly bind failure logging, set the registry value [HKLMSoftwareMicrosoftFusion!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 [HKLMSoftwareMicrosoftFusion!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()

  2. Marcelo> Thanks for that tidbit! I had the same problem, this helps a lot.

    Taylor> Much thanks for the write-up, I agree, it’ll be nice in the future if you can add a reference to a project/task. Let’s cross our fingers.

  3. My problem was, every time I edit my script task, my dll`s references disappear. So, I had to do step 5 again. The work around was:

    Go to script task properties, change the delay validation to true.

    This article is great!
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *