Saturday, 21 May 2011

Referencing a custom assembly inside a Script Task

Case
I created an assembly with some often used methods. How can I reference to this dll inside a SSIS Script Task so that I can use these methods?

Solution
This is possible, but requires some additional steps before you can use the methods. To start with, you will have to add the dll to the GAC. Only assemblies from the GAC can be referenced. For this example I will use the isCorrectEmail method from the regular expression article from some weeks ago. It's made with SSIS 2008 on Windows 7. Some of the steps could differ a little bit on other systems.

1) Class Library
Start Visual Studio and create a new Class Library project (C# in this example).
Class Library project



















2) The example method
Add the following script to the ScriptMain.cs file. I created a static class EmailMethods with one static method IsCorrectEmail. This method will validate the format of an email address and returns True or False.
// C# code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;       // Added

namespace myMethodsForSSIS
{
    // A static class with email methods
    public static class EmailMethods
    {
        // A boolean method that validates an email address     
        // with a regex pattern.     
        public static bool IsCorrectEmail(String emailAddress)
        {
            // The pattern for email         
            string emailAddressPattern = @"^(([^<>()[\]\\.,;:\s@\""]+"
                + @"(\.[^<>()[\]\\.,;:\s@\""]+)*)|(\"".+\""))@"
                + @"((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
                + @"\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+"
                + @"[a-zA-Z]{2,}))$";
            // Create a regex object with the pattern
            Regex emailAddressRegex = new Regex(emailAddressPattern);
            // Check if it is match and return that value (boolean)
            return emailAddressRegex.IsMatch(emailAddress);
        }
    }
}
Now build (release) the project to check for errors.

3) Strong name
We have to strong name the assembly before we can use it. More about that in this Codeguru article: Giving a .NET Assembly a Strong Name. Open the Visual Studio 2008 Command Prompt (in Windows start menu). Browse to your project folder and execute the following command to create a key file: sn.exe -k PublicPrivateKeyFile.snk
Microsoft (R) .NET Framework Strong Name Utility
















4) Add key file to project
Back to Visual Studio. Right click the project file and add the newly created key file to the project. See image for more details.
Add existing item














5) AssemblyInfo
Now we have added the key file to the project, we also have to add it to the AssemblyInfo.cs file. Which can be found in your Solution Explorer in the folder Properties. Add the following rows and build the project:
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("PublicPrivateKeyFile.snk")]











6) Add the assembly to GAC
Before we can use our assembly, we have to add it to the global assembly cache (GAC). Open the Visual Studio 2008 Command Prompt again (for Vista/Windows7/etc. open it as Administrator). And execute the following command.
gacutil /i d:\myMethodsForSSIS\myMethodsForSSIS\bin\Release\myMethodsForSSIS.dll
Microsoft (R) .NET Global Assembly Cache Utility














7) The SSIS project
Start Visual Studio and create a SSIS project. If Visual Studio is still open, close it first because it caches the GAC on start.


8) Variable
Open the default package and create a String variable named email and fill it with a correct email address.
String variable named email











9) Script Task
Add a Script Task to the Control Flow and edit it. Add the variable from the previous step as a ReadOnly variable.
ReadOnlyVariables




















10) Add reference
Edit the script and add a reference (menu project). Browse to the release folder of your assembly and select the dll file. See picture for more details. (Don't forgot to save afterwards. Click Save All to save adding the reference!)
Add reference to assembly
















11) The script
Add the Using and the Messagebox to the script.
// C# code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using myMethodsForSSIS;                 // Added

namespace ST_28f906d94d9745d6a375b42d428da87d.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // Messagebox True or False
            // indicating that the email
            // format is correct or not
            MessageBox.Show(EmailMethods.IsCorrectEmail(Dts.Variables["email"].Value.ToString()).ToString());

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}


12) The result
Now execute the Script Task and see what happens. Add for example an extra @ to the email address and see what happens if you execute it again.

Note 1: this can also be used for Script Components in exacty the same way.

Note 2: Instead of using a reference to the project folder, some people prefer to copy the dll to C:\WINDOWS\Microsoft.NET\Framework\<version>  and reference to that file. However for SSIS 2005 the dll should be in this folder: c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\

18 comments:

  1. I was able to add the DLL using gacutil.exe, however, the DLL I'm using references an external XML file. Because of the way the DLL is registered, it is looking for this file in a directory that doesn't exist and cannot be created (C:\WINDOWS\assembly\GAC_MSIL\\1.0.0.0__a57a5eb9b71a56f7\.xml).

    Any ideas as to how I can make it find my XML file?

    ReplyDelete
    Replies
    1. I have to examine your code to see what's wrong, but it sounds like a .Net problem instead of a SSIS problem. In one of the appropriate MSDN forums you will get more/faster/better answers: http://social.msdn.microsoft.com/Forums/en-US/categories

      Delete
  2. Chris, were you able to reference the xml config file in any way? I am running into the same issue. I have a web service proxy and it's end points are specified in the config file. But the SSIS reference is not finding it.

    ReplyDelete
  3. I was able to add assembly on my DEV machine, but on production i do not have gacutil.exe, in that case how to add dll to gac?

    ReplyDelete
    Replies
    1. try drag and drop the DLL into c:\windows\assembly or if it's .Net 4 in C:\Windows\Microsoft.NET\assembly\****\

      Delete
  4. hi Joost, I am using VS2012 ultimate version with Business Intelligence. Created .dll and deployed to GAC. In my SSIS package, referenced this .DLL but still failed to execute...

    ReplyDelete
    Replies
    1. What's the exact error? Have you pressed Save All after adding the reference?

      Delete
  5. Hi Joost.. thanks for this article, it's well written.

    I was wondering whether we can have a simple method to share user defined functions. So for eg, I would like to have all the functions that I want to share in one .CS file or one .VB file, and just be able to add the file from Solution Explorer in any project (or .dtsx package). I would then would like to simply call the functions defined in that .VB file to be used in Script Component. Is it possible?

    The requirement is that I should be able to add or edit any function in the shared .VB file and it should reflect in the project immediately.

    Btw, the functions that I want to share needs to accept parameters and return values too. Any pointers would be welcome.

    ReplyDelete
    Replies
    1. You can add C#/VB.NET files to the solution explorer of the VSTA project, but it will be included in the package itself. So you will get copies in each script task/component instead of one source file. One alternative you could try is to change the template (not supported by Microsoft)

      Delete
    2. Ok, thanks Joost. I will try that and post if any success. :)

      Delete
  6. hi Joost, Sorry not to update you but I am able to refer to a .dll on my local for my development work. Now, my package is fine and I want to deploy to a remote server for testing purpose. Should I just copy the already GAC .DLL from my local machine to the remote machine? where exactly should I copy to?

    Thanks

    Hui

    ReplyDelete
    Replies
    1. Yes you should deploy the assembly (dll) to the GAC on the remote/test server. That can be done with GACUTIL if visual studio is installed. Else you should create an installer or use powershell to deploy it in the GAC of the remote machine.

      Delete
  7. hi Joost, in my local dev, I used VS2012 Ultimate to compile my .DLL using .NET 4.5.
    On my target SSIS server, .NET 4.5 has been installed. However, when I used VS2010 on target SSIS server to open the pkg I've created in VS2012, the script task which is referencing the .DLL gave me a blank page, no code at all. I am wondering if VS2010 does not support .NET 4.5?

    ReplyDelete
    Replies
    1. AFAIK SSIS 2012 supports up to 4.0, so not 4.5

      Delete
  8. also is there any way to tell which .NET framework was my .DLL built upon? thanks

    ReplyDelete
  9. Great tip. Will this work when referencing dll work when you deploy SSIS package to SQL Server ?

    ReplyDelete
    Replies
    1. Yes, but you need to have that assembly in the GAC of that SQL Server.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...