Business Process Integration

In SQL Server SSIS you can use the Script Task to add your custom code (for eg. in C#).

SSIS makes it very easily to debug your package, and also debug your custom source code. It is only a pity that you can’t unit test your own script code. I searched on the internet, but I found only how to test your SSIS (DTS) package via unit testing. I dived into this and created a generic approach for unit testing .Net code from a SSIS package.

First I researched what happens with the source code and how this will be executed runtime. The source code is stored in the package (mypackage.dtsx). Each Script Task will lead to a new project and also a new namespace. The namespace looks something like: ‘ST_1234567890abcdef01234567890abcde.csproj’. With this knowledge you can find your source code in the package, but better; also the binary, the .dll, the compiled code. Each time you change your source code, SSIS will compile the code and put the dll as Base64 string in the SSIS package. This looks like:

}]]></ProjectItem><BinaryItem Name="\bin\release\st_1234567890abcdef01234567890abcde.csproj.dll">TVqQAAMAAAAEAAAA//8AALgAAAAAAAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAgAAAAA4fug4AtAnNIbgBTM0hVGhpcyBwcm9ncmFtIGNhbm5vdCBiZSBydW4gaW4gRE9TIG1v
ZGUuDQ0KJAAAAAAAAABQRQAATAEDAP5jD0wAAAAAAAAAAOAAAiELAQgAAD4AAAAIAAAAAAAAHlwA…

With this knowledge, we can read the package, access the Base64 string with namespace as search item (lowercase). This Base64 string can be stored as dll on your file system. It is possible to reference this dll in a unit test project. But the problem is that the signature of the dll will change when you change your package. A better approach is to load the dll into memory and use the properties and methods using reflection. Here are some code snippets:

Load package:

XmlDocument package = new XmlDocument();
try
{
                package.Load(pathAndFileToYourDtsPackage);
}
catch (Exception ex)
{
                throw new Exception("your message");
}

Access the Base64 string

string xPathToBinary = @"//BinaryItem[@Name='\bin\release\" + namespaceClass.ToLower() + ".dll']";
XmlNode element = package.SelectSingleNode(xPathToBinary);

Save your dll

using (FileStream fs = File.Create(yourDllFileName))
{
                byte[] buffer = Convert.FromBase64String(element.InnerText);
                fs.Write(buffer, 0, buffer.Length);
}

Now you can use reflection to load your dll, instantiate the class and access methods and properties. Here are some code snippets:

_assembly = Assembly.LoadFile(_dllFileName);

string typeName = namespaceClass + "." + className;
_classType = _assembly.GetType(typeName);

_classInstance = _assembly.CreateInstance(_classType.FullName, false, BindingFlags.CreateInstance, null, yourConstructorParameters, System.Globalization.CultureInfo.InvariantCulture, null);

Now you can use methods like GetMethod, InvokeMember and GetProperty to access your methods and properties.

You can write your unit tests in a separate test project, and use above methodology to unit test your Task Scripts. For now I only see one disadvantage: you work with ‘late binding’, so you don’t have access to your methods and properties straight away. But when everything is in place and ‘green’, then it doesn’t matter anymore (my opinion).

Do you think there are other possibilities? Other approach so you can use early binding? Please let me know!