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\
